Extra Quotation Mark in SQL Query

Question

I'm studying SQL injection and I got following example on this website:
https://owasp.org/www-community/attacks/SQL_Injection
Considering it is a professional website, there should not have been error in the code.

Text from the web:
The following C# code dynamically constructs and executes a SQL query that searches for items matching a specified name.

string query = "SELECT * FROM items WHERE owner = "'"
                + userName + "' AND itemname = '"
                + ItemName.Text + "'";

The query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character...

However, I could not understand the code as I notice that there is an extra " after the =
The code should have been:

string query = "SELECT * FROM items WHERE owner = '"
                + userName + "' AND itemname = '"
                + ItemName.Text + "'";

Can someone please tell me if I'm wrong. Thank you.

You should keep one thing in mind, you are using one language to create a code in another language, so the text will have syntax from both those language. You should try to identity which symbol belongs to which language. The single quote here is for sql, double quote is for c#. You are building a sql query using c#. In c#, it is a string and string is quoted with double quotes. The sql query you are building has a string argument inside it which in sql land, should be inside single quote. — Mat J, Mar 20 '21 at 08:51

Caius Jard · Accepted Answer · 2021-03-20T08:53:24.687

Yes, you're correct, this character is a typo on their behalf; it wouldn't even compile in C#, and should not be present:

string query = "SELECT * FROM items WHERE owner = "'"
                                                  ^

It might be easier to see about SQL Injection if we make it simpler, with just one variable:

var sql = "INSERT INTO users VALUES('" + username + "');"

When username is Sys_Glitch this works fine, the SQL becomes:

INSERT INTO users VALUES('Sys_Glitch');

When username is Hah'); DROP TABLE Users;-- it's a problem:

INSERT INTO users VALUES('Hah'); DROP TABLE Users;--');

We've convinced the app to construct a valid SQL by putting a username, then more characters that will finish off the SQL INSERT, and run another one, deleting the users table. We end the username with a comment -- to prevent a syntax error, but we could have done something else, so long as the result is valid, like Sys_Glitch'); UPDATE Users SET Role='Admin' WHERE Name = 'Sys_Glitch, which not only inserts a user but then upgrades them to admin.

INSERT INTO users VALUES('Sys_Glitch'); UPDATE Users SET Role='Admin' WHERE Name = 'Sys_Glitch');
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
                          This part is from the text supplied by the user

Extra Quotation Mark in SQL Query

1 Answers1