Lets say I want to read user data from a table when user provides username and password.
My plain text query looks like this:
select * from Users where UserName = '{THIS WILL BE PROVIDED BY USER}' and Password = '{THIS WILL BE PROVIDED BY USER}';
Now I use SQL parameters to prevent SQL injection problem with the aforementioned query:
SqlCommand command = new SqlCommand();
command.CommandText = $"select * from Users where UserName = @UserName and Password = @Password";
command.Connection = connection;
SqlParameter parameter = new SqlParameter("@UserName", SqlDbType.NVarChar);
parameter.Value = "{THIS WILL BE PROVIDED BY USER}";
command.Parameters.Add(parameter);
SqlParameter parameter2 = new SqlParameter("@Password", SqlDbType.NVarChar);
parameter2.Value = "{THIS WILL BE PROVIDED BY USER}";
command.Parameters.Add(parameter2);
If I use the typical value like this ' OR 1=1 -- to reproduce the SQL injection, it does not work with parameterized query.
Why parameterized queries are able to prevent SQL injection?
Though query is parameterized, eventually it has to be turned into a "usual" query at the time of execution. When we are setting the malicious value for a parameter, why does not it take effect?
What happens under the hood?
