Re: A little question about Sql Injection in SP.
No, this input is not injecting anything. But suppose this:
inUsername = ''
inPassword = '\' OR \'\'=\''
The quotes are a little bit confusing, so let me put it down again, now with the actual string values that will result. I'll use braces as the outer delimiters, Ok?
inUsername = {}
inPassword = {' OR ''='}
Now, lets see what happens when we plug it in.
In the first case, the *value* of the parameters is evaluated. That is, the value in the columns is matched to the value in the variables. This is not injection, just comparison of values. The query will just look for users with that funny empty username, and with that quirky password that looks a bit like sql.
The point is that there is never any chance that the values are interpreted as sql language elements.
How different is this in the second statment! Let's plug the values right in, see what happens. I'll use []'s to mimic the outer double quotes. I'll leave the {}'s in place for a while, just to make sure what's going on, Ok? So,
CONCAT("SELECT * FROM users WHERE username ='", inUsername, "' AND password='", inPassword, "'");
becomes:
[SELECT * FROM users WHERE username ='{}' AND password='{' OR ''='}']
Ok, youre ready? No let's get all the delimiters out, and see the final result:
SELECT * FROM users WHERE username ='' AND password='' OR ''=''
The last piece is the injection:
OR ''=''
What doe you think this WHERE clause will do, huh? Right! it is always true!
Now, try for yourself, what would happen now:
inUsername = {' UNION ALL SELECT schema_name FROM information_schema.schemata --}
would eventually become:
SELECT * FROM users WHERE username =''
UNION ALL
SELECT schema_name FROM information_schema.schemata
-- ' AND password=''
(I put some linebreaks in, but you can convince yourself that these dont affect the query)
Right! The cracker has succeeded in eliminating your original query from the result, and has turned your application into his own private query command tool.
You see, assuming there are no users with the empty string as username, your query will never retrieve any records. The UNION ALL gives him an opportunity to inject an entire SELECT expression, and the -- blanks out the rest of your query. Chances are that his query will retrieve another number of columns than your original query, but hey, we have patience. It's just a matter of time, adding another column until you strike oil. Happy footprinting everybody!
Finally, he's ran through all the data in the information schema, and has found out what tables he's access too. And functions. And procedures. And if you smucked up the security, this is now known to him too.
I hope you understand, please let me know if not.
CU
Edited 2 time(s). Last edit at 01/12/2006 09:33AM by Roland Bouman.