While testing, why not add a debugging statement that logs your SQL query before executing it?
Then try running it in your database directly and see what error message the DB returns (if any)?
If there's no inadvertent automatic escaping of
' in your framework, it seems your query:
string sql = "select * from Users where UserName='" + userName + "' and Password='" + password + "'";
string sql = "select * from Users where UserName='' union select * from products --' and Password=''";
Can you verify that a string like this appears in your log (that your framework didn't inadvertently escape
Can you execute that specific SQL string? Does it return an error message for your specific schema/tables? (I don't program in .NET, use SQL-server, or have your database (I tend to use linux stack; e.g., postgresql). )
But running on a sample DB I had where I tried a UNION of two tables like
SELECT * FROM table_a where name='' union select * from table_b, I get a error from my DB: SELECTs to the left and right of union do not have the same number of result columns. My table_a had 7 columns and table_b had 11 columns. Thus a union of the 7 columns of table_a and the 11 columns of table_b won't work.
This makes sense if you look up what SQL UNION does:
The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
PS: The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION.
So you have to guess the schema and make sure it matches. That is if the original SQL query had a
select * from Users, and the columns of Users is user_id (INT), userName (VARCHAR), password (VARCHAR), you must make sure your UNION asks for three specific fields that have datatypes int, VARCHAR and VARCHAR that are present in the second table. Also if you don't want results from the first query you may want to add an
AND 1=0 to your