SQL Injection Attacks - Are You Safe?

[tdarling]

These comments are in regards to the SitePoint.com article 'SQL Injection Attacks - Are You Safe?'.

The author writes:
"It's no good having a text box on a form that can accept 50 characters if the field you'll compare it against can only accept 10. By keeping all text boxes and form fields as short as possible, you're taking away the number of characters that can be used to formulate an SQL injection attack."

Not true!!! This provides no protection at all. The user can simply copy the HTML from the Form page to their own website and change the text box size, and submit whatever they want. All text inputs have to be truncated to a known size upon being received by the program!

[datune]

Do you mean that one could for example do this

Let's assume the original website with the form is called
websitenr1.com
it holds a form like this

<form name="postit" action="index.php?action=whatever>
<input type="text" name="one" maxlength="10">
<input type="submit" name="submit" value="submit">
</form> 

Now the malicious user copies the html including the form, uploads it to his own webserver and changes the orginal form into this

<form name="postit" action="http://www.websitenr1.com/index.php?action=whatever>
<input type="text" name="one" maxlength="50">
<input type="submit" name="submit" value="submit">
</form> 

Can he now really submit this?
And if so, what damage is done?

And the "exploits" described in the above article will only work on MS SQL Servers, and for all those using MySQL(no idea if it works with other RDBMS) you can use the PASSWORD function.
For example

$sql = "SELECT * FROM user WHERE userid='$uid' AND password=PASSWORD('$pwd')"; 

Of course you also have to do this when creating users.

And in response to

All text inputs have to be truncated to a known size upon being received by the program!

Could you please tell me which RDBMS can not handle this by itself?

Unless of course you want to use the Field type TEXT etc.. but then, why would you want to limit that in the first place? You can as well use VARCHAR(255 characters), and everything that goes beyond that...well i can not think of a single application that would have to rely on input not being bigger then (for example) 263 characters.....(id love someone to prove me wrong, and note that i am talking about WEB applications, although...)

So basically, what's it all about? Too me it looks like a lot of hot air, if you get me.

Best regards from Vienna,
datune

[tdarling]

Yes, your example is correct.

What is the damage? I can insert something like:
'; DROP TABLE users; --

into the username HTML textbox. Usually I wouldn't be able to do that because if all usernames are <10 chars, the unmodified HTML would not let me enter the 26 chars above.


By doing that, my SQL query which starts out as:
"SELECT * FROM users WHERE userName='" + userName + "' AND password='" + password + "'"

Will become:
SELECT * FROM users WHERE userName=''; DROP TABLE users; -- ' AND password=''

That's the problem. It has nothing to do with the DB checking the size of the fields since we are tricking the DB. The program has to truncate the username HTML Form input before we run the above query.

[datune]

Ermm. don't misunderstand me now, but did you ever try to execute such a query as like the one given above?

All i get is this
Database error: Invalid SQL: SELECT * FROM user WHERE userid='datune' AND password=PASSWORD(''; DROP TABLE users; --')
MySQL Error: 1064 (You have an error in your SQL syntax near '; DROP TABLE users; --')' at line 1)
Session halted.

So this is bullex my friend, at least on MySQL using PHP (no idea if this is handled different with other configurations.)

Second thing is, to be able and do any damage at all you would have to know my table names, how are you going to do that?

See, it bothers me that people are yelling out so much hot air, about the same crap must have happened with cookies, cause the "average" internet user still believes cookies are little monsters that can eat your system (in a way of speaking).

Show me a working example where you can delete my tables even if you know the names. The above one will not work.
Ill give you a field with no length limits, you can enter whatever you want, it will not work.

Oh, and if i use sessions, you will have to close your browser each time and reopen it to be able and enter your "hack" again, so the people trying to do damage will have a hard time using modified stuff like Golden Eye along with word lists etc...

[atomical]

I don't think any of this applies to a php/mysql package. If the string is too long to fit in a collumn mysql automatically truncates it, at least that's what it does for me.

[tdarling]

Correct :

SELECT * FROM user WHERE userid='datune' AND password=PASSWORD(''; DROP TABLE users; --')

MySQL Error: 1064 (You have an error in your SQL syntax near '; DROP TABLE users; --')' at line 1)
Session halted.


Is an invalid SQL command, but you're close! It looks like you entered
'; DROP TABLE users; --

in the password field. Enter it in the userID field instead.

And, yes, I have seen this work. I think it will work on your website from what you posted above if you enter it in the username field.

[datune]

Originally posted by atomical
I don't think any of this applies to a php/mysql package. If the string is too long to fit in a collumn mysql automatically truncates it, at least that's what it does for me.

Yup that is true, but i think he ment if you want to limit a field other then the default given by your DBMS.
For example, a VARCHAR field will truncate if there are more then 255chars, per default.
Whereas using a TEXT field it is limited with 65000 something chars....

[tdarling]

"I don't think any of this applies to a php/mysql package. If the string is too long to fit in a collumn mysql automatically truncates it, at least that's what it does for me."

No no.. you're missing the point. All DBs will do that (or throw an error).

The key is, the program that handles the logins runs some code like this:
"SELECT * FROM users WHERE userName='" + userName + "' AND password='" + password + "'"

And we enter something into the userName field/variable so it runs a SQL command like this: (remember -- is the comment command for SQL, so it ignores the rest of the line):
SELECT * FROM users WHERE userName=''; DROP TABLE users; -- ' AND password=''

[datune]

tdarling
I tried it in the username field as well, so no it will not work. I told you, it is crap. I tried all possible variations.

Look, im not saying it is not a good style of programming if you CHECK incoming data before passing it to the programm, but that is two different peace of cakes.

You try again, show me a working example

[voostind]

In the example tdarling gave a piece of 'normal' input is translated to a real SQL query. His example might not work for you, but vary it enough and it possibly could; it's not crap.

There is, however, a simple solution to this problem: be aware of the values you're sending to the database.

When you're sending a string value to the database, you have to surround it with single quotes, and replace all single quotes inside the string with two single quotes next one another. This will make it impossible to put an SQL query inside a normal string.

You must know you're types. When the user must supply his/her age to be stored in an INT-field, always, ALWAYS make sure it is actually an integer, before sending it to the database. An '(int)$value' will do that for you. (If $value was a string, it will simply become 0.) Likewise for floats and any other kind of type you're using.

Vincent

[MattR]

Typically most database libraries will not allow you to run more than one query on a call (e.g. you couldn't run 'SELECT * FROM user; DROP TABLE user'; it would simply ignore everything after the first semicolon (or die with an error). As we all know in our programming languages command terminators are not required, nor do they work typically when inserted (on Sybase/MS SQL you don't have to run \n GO in your DB call, nor do you end your MySQL/Oracle SELECT with a semicolon).

The shell, of course, operates differently.

The more likely attack would allow a cracker to elevate his or her privlidges to wreak havoc through your pre-built administrative interface.

For example, in the username/password combo we have this pseudocode:

database.exec( "SELECT 1
                  FROM user
                 WHERE username = '" + username + "'
                   AND password = '" + password + "'" )

if database.result is 1 
  user authenticated, log in user
else
  user didn't provide correct combo

What if you sent in the following combination:
username: administrator (or whatever a known admin account is)
password: ' OR 1=1 --

(-- is a start comment character in SQL)

Your SQL statement would look like this:

SELECT 1
  FROM user
 WHERE username = 'administrator'
   AND password = '' OR 1=1 --'

So you wouldn't need a valid password since 1 is always equal to 1. Your code could/would log the malicious user as an admin.

Another solution is, of course, to check the returned username and password combo to the user-entered combo, but it is still imperitive that you be sure to:

For numeric data (e.g. numbers), strip out all non-numeric (0-9) characters.

For string data, strip out (or escape) all string terminating characters.

In the above example, if we escaped the single quote in the password field we'd have:

SELECT 1
  FROM user
 WHERE username = 'administrator'
   AND password = ''' OR 1=1 --'

Meaning it would look for a password which was the string literal ' OR 1=1 --, which wouldn't match anything.

Hard-and-fast Rule:
Always escape string entry
Always sanitize numeric entry

[datune]

Originally posted by voostind
In the example tdarling gave a piece of 'normal' input is translated to a real SQL query. His example might not work for you, but vary it enough and it possibly could; it's not crap.

Voostind, it is crap. Because NONE of the examples work on either my local configuration nor on my hosts webserver.

Ive tried many variations, and basically telling stuff like this is spreading bullex amongst the people (same with cookies). [no offence taken ok

I agree with everything else youve written, and like ive said before, you should of course always check incoming data and make sure you get what you expect, using, like you have written too, typecasting etc...

But all the rest is just a lot of hot air.
My home configuration is a WAMP (Windows, Apache, MySQL, PHP), and on my host it is a LAMP(Linux, ...) config.

So what other configs do you have in mind that this might have affect on?
Don't tell me if you vary it enough it will work, show me a working example using common sense, just ONE

MattR
I am sorry, the logic behind your given example might be correct, but it will still not work out.
It will still return an error, at least on both a WAMP and LAMP system.

Again, i have no idea if this works on other setups, if so i would really like to know (would be valuable information)

From what i remember SQL Injection only applies to MS SQL, but of course i might be wrong there...

So unless either one is going to supply a working example im gonna continue to say this is crap, cause sofar it is.

[atomical]

I side with datune. I think it's bogus hype.

[MattR]

datune, it would be worth discussing exactly how you have your data set up. What is your PHP code including SQL? What error are you receiving?

[datune]

MattR, excuse me, but before asking me something like that you might want to read the thread first

And this is not about me, were talking general here.
Anyway, im getting tired of this thread... feels like repeating myself all over the time

Im off for bed now

[MattR]

I did read the thread -- perhaps you neglected to read my post illustrating a different exploit? If not, and you tried the exploit, and it failed, I would like to know why since it 'works' on my end. You have not provided the error in this case, other than "I am sorry, the logic behind your given example might be correct, but it will still not work out. It will still return an error, at least on both a WAMP and LAMP system."

It should not return an error.

[voostind]

As we're more or less throwing around insults here, let me try one:

My home configuration is a WAMP (Windows, Apache, MySQL, PHP), and on my host it is a LAMP(Linux, ...) config.

<Shudder>

As PHP is maturing and more 'professional' programmers are working with it, we will hopefully see one of two things:
1. MySQL will be abandoned and a proper DBMS will be used instead.
2. MySQL will mature enough to be called a DBMS.

I know it doesn't add anything to this thread, but anyone claiming MySQL is a proper DBMS can't be considered a 'professional' or an 'expert'. 'Amateur' is the closest that comes to mind.

MattR gives a good explanation of what could possibly happen if you don't write code securely. The methods he describes are not pulled from some theoretical book; they have actually happened. Maybe his method didn't work for your particular set-up (and good for you), but that doesn't mean that the whole 'theory' is bullocks. That's like saying 'I saw a bird that couldn't fly even while it had wings, so no bird can actually fly at all.'

Vincent

[datune]

MattR
Of course i tried your exploit, but i guess i misunderstood you, i didn't get it that you wanted to see the error that is returned when i tried your exploit.

This is the error i get, and datune is a known account
using the PASSWORD function
Database error: Invalid SQL: SELECT * FROM user WHERE userid='datune' AND password=PASSWORD('' OR 1=1 --')
MySQL Error: 1064 (You have an error in your SQL syntax near '')' at line 1)
Session halted.

NO password function
Database error: Invalid SQL: SELECT * FROM user WHERE userid='datune' AND password='' OR 1=1 --'
MySQL Error: 1064 (You have an error in your SQL syntax near ''' at line 1)
Session halted.

If you want more information let me know

voostind

Originally posted by voostind
As we're more or less throwing around insults here, let me try one:

Im sorry if you feel that way, i don't feel like that.
If you can't make a difference between a discussion where people have different oppinions and are merely throwing arguments against each other...

As to the rest of your reply, with all due respect, now youre the one who has proven to be unproffesional.

First of all, it is very very unproffesional to claim that MySQL is amateurish, this again proves that you didn't do your homework well.

Nobody is going to say that MySQL is THE RDBMS ever, and of course none is going to use MySQL for Bank transactions etc..

But if it comes to webapplications, it is unbeatable in the Open Source sector, and personally i think using (for example) Oracle for a "simple" website like (again for example) Sitepoint is a huge overkill.

Also voostind, i never ever said that what MattR has written is impossible to do, but EVERY programmer who can't handle SIMPLE stuff like that should take a book and start over again.

And i tried loads of variations to see if i can get this to work, i couldn`t even get it to work without preventing ANYTHING.

Having that said, i think it is as someone else said, bogus hype, which has been overhyped, im not saying it can't be done (who am i to say that), considering all the possible configurations etc.. but i don't think this generally applies to an average WAMP, LAMP whatever setup.

Look, it is really simple, none of the given examples work,in the meantime this has been tried on 4 totally indepent host (i asked some "collegues"[no idea how to spell this]) and it did not work on none of the systems available. End of story

Have a nice day!
datune

[MattR]

For the password function you obviously need to close the parens:
password: ') OR 1=1 #'

I read up on MySQL and apparently the comment -- must be followed by a space (or use the # sign).

So for your second try:
' OR 1=1 -- '
or
' OR 1=1 #

Both should work.

There are also more complex and subtle methods of extracting more data from your server by appending extra queries to your string.

Say you had something showing usernames and emails for particular users, e.g.:

SELECT username,
       email
  FROM user
 WHERE username = '$username'

If you change username to be this:

'
UNION 
  SELECT username,
         password (depends on data type, maybe would have to cast it to something else)
    FROM user
   WHERE username = 'admin

You could have it show the password for the admin account. Earlier versions of MySQL do not support UNION but MySQL4 does, so it can certainly be an issue.

[freddydoesphp]

This post is funny!

tdarling, people have been claiming this to be a secuirty hole when using MySQL and PHP on the web for years now, I remember having this same discussion with MattR and a few others about a year ago. I have found noone who can show this to actually work, everyone speaks of the theory. Show us the money.

[Skunk]

I'm pretty sure the reason this exploit doesn't work on default PHP installations is that magic_quotes is on by default. If magic quotes is on any quotes in input from forms will be escaped without you having to do anything - effectively making your script immune to SQL injection attacks.

I haven't tried it myself, but I suspect that turning magic quotes off (and not specifically using addslashes() in your scripts) would open you up to SQL injection attacks.

[trickie]

instead of getting all fiesty, why doesn't everyone just agree that some people have found security problems in something, and it pays to be concious (bad spelling) of these problems even if you can't replicate them. If they break some variation of setup somewhere, then we it pays to at least acknowledge the problem. I'd say that would be a professional stance.

By they way i'm not trying to jump into the fray, just that everyone had good points, just listen and learn from each other

[datune]

Originally posted by MattR
For the password function you obviously need to close the parens:
password: ') OR 1=1 #'

I read up on MySQL and apparently the comment -- must be followed by a space (or use the # sign).

So for your second try:
' OR 1=1 -- '
or
' OR 1=1 #

Both should work.

There are also more complex and subtle methods of extracting more data from your server by appending extra queries to your string.

Say you had something showing usernames and emails for particular users, e.g.:

SELECT username,
       email
  FROM user
 WHERE username = '$username'

If you change username to be this:

'
UNION 
  SELECT username,
         password (depends on data type, maybe would have to cast it to something else)
    FROM user
   WHERE username = 'admin

You could have it show the password for the admin account. Earlier versions of MySQL do not support UNION but MySQL4 does, so it can certainly be an issue.

Well ill be damned!!!!
This time MattR i got it to work, but ONLY and ONLY if i did not use the PASSWORD function.

Ok, im gonna try to summ it up now.

using the following query

//...
$uid = isset($_SESSION["uid"]) ? $_SESSION["uid"] : '';
$pwd = isset($_SESSION["pwd"]) ? $_SESSION["pwd"] : '';
//....
$sql = "SELECT * FROM user WHERE userid='$uid' AND password=PASSWORD('$pwd')"; 

The following error is returned

Database error: Invalid SQL: SELECT * FROM user WHERE userid='datune' AND password=PASSWORD('' OR 1=1 #')
MySQL Error: 1064 (You have an error in your SQL syntax near '' at line 1)
Session halted.

BUT
If someone for whatever reason builds a query as follows

//...
$uid = isset($_SESSION["uid"]) ? $_SESSION["uid"] : '';
$pwd = isset($_SESSION["pwd"]) ? $_SESSION["pwd"] : '';
//....
$sql = "SELECT * FROM user WHERE userid='$uid' AND password='$pwd'"; 

This crap actually works!

But now comes the real fun part!

Even if using this query

//...
$uid = isset($_SESSION["uid"]) ? $_SESSION["uid"] : '';
$pwd = isset($_SESSION["pwd"]) ? $_SESSION["pwd"] : '';
//....
$sql = "SELECT * FROM user WHERE userid='$uid' AND password=PASSWORD('$pwd')"; 

And IF you know an existing account name and you enter
') OR 1=1 -- '

Guess what? It works too!

So finally somebody was able to provide me with a working example.

So, MattR, what you suggest to prevent this peticular example?

[MattR]

See? I told you it would work. And yes I thought I specified in your case using the 'password()' function (which I would heartily say not to use since it is MySQL specific and nonstandard!) you have to use the altered version of the command.

Anyway, the cure is, as I posted before, to run your variables through addslashes and intval.

For example in your case (you can optimize the PHP code any method you would like, this is just an example):

//...
$uid = isset($_SESSION["uid"]) ? $_SESSION["uid"] : '';

// Strip out all non-numeric data
$uid = intval( $uid );

$pwd = isset($_SESSION["pwd"]) ? $_SESSION["pwd"] : '';

// Escape quotes in string
$pwd = addslashes( $pwd );

//....
$sql = "SELECT * FROM user WHERE userid='$uid' AND password=PASSWORD('$pwd')"; 

So, that will do the following thing:

$uid = "4 OR userid IS NOT NULL #";  // uh-oh has code to break the query and hack in!
$uid = intval( $uid );               // $uid is now 4

$password = "') OR 1=1 -- '";        // password is unescaped!

$password = addslashes( $password ); // password is now a string and perfectly harmless 

P.S. If $uid is a number, then do not enclose it in quotes. It should be like this:

SELECT *
  FROM user
 WHERE userid = $userid

[datune]

And yes I thought I specified in your case using the 'password()' function (which I would heartily say not to use since it is MySQL specific and nonstandard!) you have to use the altered version of the command.

Now that doesn't make sense.
If programming something specifically and knowing it's only going to be used on MySQL, why not use it? Being non-standard (as you claim it to be, although i know so many programmers using it, but anyway) it's an added plus.

Cause malicious users will not come up with the correct command

Well, this was quite interresting, and one mistake i admit is the following:
Usually i ALWAYS do the following with incoming data from $_POST or $_GET

$_POST = array_map('addslashes',$_POST); 

But this time with using sessions i somehow totally forgot about it

So do you think that the example given by tdarling can be done as well?
What made me wonder in the first place is how are you going to find out the names of my tables?

And i don't think you can do two querys on the same line can you?