Could anyone explain to me what SQL injection really means?

[dullboy]

For example, we have a username and password as 'tom' and 'secret' separately. However we might input tom and i_don't_know' or 'x' = 'x to confuse SQL server. How does that happen? Thanks for your inputs.

[mmetzger]

SQL Injection is basically a situation where you don't validate your input and the input modifies your SQL statement in an unforseen way.

Take for example, the following piece of C# code (this is true regardless of the database).


bool authenticateUser(string username, string password)
{
SqlConnection conn = new SqlConnection(//connectstring);
SqlCommand cmd = "select id from MemberTable where (username = '" + username + "' and password='" + password + "')";
conn.Open();
string id = cmd.ExecuteScalar().ToString();

if (string != null)
{
return true;
}
return false;
}


Now this code is ugly for a few reasons, but specifically pay attention to the SqlCommand line. The SQL Query is built such that we rely on the input from the client (being web browser, windows app, etc.) to authenticate the user.

If we give the values of "fred" and "password", we end up with a SQL statement that looks like:

select id from MemberTable where (username = 'fred' and password='password')

As such everything will work as designed. If fred exists and he has the password of "password", he will be authenticated, otherwise he won't.

But what happens if we pass values such that we modify the SQL statement? Or in your example, if we pass "blah' or ('x' = 'x')" for both entries. In this case, we end up with:

select id MemberTable where (username = 'blah' or ('x' = 'x')) and password='blah' or ('x' = 'x'))

When the statement is executed, it will return a value other than null. So you get authenticated without knowing any information.

Now there are a few ways around it. The easiest is to use Stored Procedures and use parameters. When you pass data as a parameter, it is treated exactly as the data type it receives, so in this case, it looks for the literal string

blah' or ('x' = 'x')

which will come back as not being in the database.

You can also do this with SqlCommand statements using Parameters.

The final method is to simply validate your input (remove or escape all ' marks, etc.) This requires knowledge of the exact interpretations of all characters, but is possible.

MSDN has a bunch of recorded webcasts on SQL security and SQL injection that go into further detail...