SQL Injection Attacks - Are You Safe?

[MattR]

It is not a part of the ANSI SQL standard, hence it is non-standard. So is LIMIT and a bunch of other MySQL proprietary features. The trade-off is, of course, that with LIMIT you gain some functionality at the expense of portability.

With PASSWORD, though, you really gain nothing other than an increased dependancy on MySQL. It provides a proprietary method for one-way hashing of a password.

A standard function, availible in virtually every programming language and library for one-way hashing is MD5. It is mathematically secure and standardized (meaning MD5 in one language is the same as MD5 in another). Further you can even perform MD5 in SQL in case both your RDBMS and programming language didn't support MD5.

Nothing is stopping MySQL from changing the way PASSWORD functions. Perhaps they change the amount of characters the password function outputs. Perhaps they change the hash function so that old hashes no longer match. There's no guarantee that will not happen with MD5, although since it is standardized the liklihood of that happening is far, far less.

Anyway, when you run array_map( 'addslashes', $_POST ); you are securing your SQL against string-based attacks. The issue is not 'crap' as you stated earlier; we have seen how it can be used to elevate user privlidges. Further it 'not working' in certain cases as again you have illustrated is not that there is not a problem, but that you already provided a fix for it, which is all the original author was trying to get you to do. It's like people saying "Buckle your seatbelt so you don't die in an accident" and you getting in an accident and saying "Hey I didn't die, what are you talking about?" when you were ALREADY wearing your seatbelt. People who don't buckle up have a much higher risk of being severely injured or killed in a car accident. To those who wear their seatbelts: "Super, just remember to wear them every time!". Similarly, SQL Injection is very real and can happen if one forgets to sanatize their user-submitted variables. However, if you sanatize them, then you won't have a problem, case closed, life is good, etc.

However in your case, there is still the issue of integer-based methods since adding slashes will not preclude a comment character or extra SQL going through. So you would need to individually run the intval function on the $_POST members which are supposed to be numeric ONLY.

Names of tables are very easy to deduce (virtually everyone uses 'user', etc.) and as your example showed if you leave your SQL queries unchecked MySQL will spit out an ugly error, typically with the SQL command echoed. That is the SECOND part to the SQL Injection Prevention -- do NOT allow your programming language to echo errors directly. Perform rudimentary error controls so that malicious users cannot gain knowledge of your data structures.

If you are using parameters, such as your $uid, make sure they do not hint as to what the internal strutures are. When I visit show_user.php?uid=bobross it is fairly obvious that uid maps to something like uid, userid, etc.. Internal variables can and should be descriptive but POST/GET/etc. vars should not betray what your internal structures are.

[datune]

Yes yes of course, i know about MD5, and i also know about everything else you said.
But keep in mind that when developing i have error echoed, of course if done it is turned off etc...

Course i know about VALUES needed to be checked in integers only, but sofar i had no need for that, as i don't have numerical data only (unless of course with math. operations.

About guessing table names, did you ever hear about $prefix?

If im ever worried about people attempting to do things like deleting my tables (which can be checked by logging errors that are spit out), i simply have the $prefix changed every day (if neccesarry even auto.,.

For example

$prefix = "TkUsLO45JK";

my tables are now all named
TkUsLO45JK_users or TkUsLO45JK_articles, have fun guessing around

[MattR]

Great, you're following all that the article stated as 'good practices'. So why the outcry about the article (or more precisely 'SQL Injection') being 'crap'?

P.S. it is not necessary to be able to enter more than one command to erase a table. All the cracker needs is to find one vulnerable DELETE query and perform the same attack as we've shown before, e.g.:

DELETE FROM user
      WHERE username = '$username'

Replace $username with something along the lines of:
' OR 1=1 #

And it has now deleted every row in your table.

[datune]

Heh! Hold it matey...
I never said the ARTICLE was crap, i said that tdarlings comment was crap, especially with the delete query, cause sofar i could not get it to work, and im trying hard, cause i like to know just how secure my code is.

I only said that I THINK what is written in the article mostly applies to MS SQL (which i believe is where the original threat came from, could be wrong though).

Also i don't believe that the stuff weve done now had anything to do with the original SQL Injection problem.

Let's face it, you ask this any RDBMS Developer and he will laugh at us, he will say, it is up to the programmer to make sure incoming data is checked etc...

Which was btw the response i got whilst speaking to someone who develops SAP applications (don't know if you know this, complicated to explain, i hardly know myself).

Anyway, im still interested MattR if you could come up with a command that could delete one of my tables provided you know the name of course. If you have a working one share it please cause i would really like to see

Cheers,
datune

[voostind]

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.

Or maybe I did my homework too well... What I say about MySQL is not a claim. It's a fact. The problem is that there are so many programmers out there who learn by books that teach bad stuff, or who don't really now what a database is... The result is that 'everybody uses it', so 'it must be good'. Crap. There are many Open Source DBMS's that are far better than MySQL. PostgreSQL is one. Sybase is another. And what about SAP-DB?

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

Actually, addslashes is (just like PASSWORD) MySQL-specific and non-ANSI. I know of one other DBMS that supports it (PostgreSQL), but that doesn't make it right. The proper thing to do is to replace single quotes with two single quotes:

$new = str_replace("'", "''", $old); 

That is ANSI, so that is what you should use.

About guessing table names, did you ever hear about $prefix?

It sucks. It's not a solution to any problem; it's only working around your own incompetence: "I don't know ho secure my DBMS properly, so if I just give my tables weird names, the problem will go away by itself." Yeah right.

Let's face it, you ask this any RDBMS Developer and he will laugh at us, he will say, it is up to the programmer to make sure incoming data is checked etc...

No he wouldn't. A 'real' RDBMS developer adds triggers and functions to the database schema to make sure the database logic is preserverd, INSIDE the database. But then, MySQL doesn't support triggers. Or functions. Or anything a proper DBMS should support.

Vincent

[Skunk]

It's damn fast though

[voostind]

It's damn fast though

Yes, but only if you use the default table types. And these are pretty useless, as they don't support any of the required DBMS properties. As soon as you need those (which is always), you must use the InnoDB table type, which isn't any faster than PostgreSQL (just to name one).

Vincent

[timnz]

Care to elaborate voostind? Not on the speed issue but the table type properties and why some are useless, and I suppose why you need them will then become self-explanatory.

Simply curious...

[voostind]

Well, there's a whole list, and I won't mention it here completely. But examples? Alright:

- Views. These are mandatory for any DBMS. The MySQL developers say "they're not that important", which should already tell you something.
- Cascades/Restricts. Although it is possible to write these in the database schema, MySQL simply ignores them. Say you have a user table with a primary key on 'login' and you refer to this table from another one. If the login is changed, all occurrences of the old name in the database must be changed to the new name. MySQL doesn't do this, so you either do it manually, or use a primary key that doesn't change (like an auto-incremented integer). The first is bad, because all database logic should be in the database and not in the (PHP) code. The second is also bad, because you don't want to change your perfectly fine database schema just because a particular piece of software doesn't know how to work with it.
- Triggers and functions. Also required to maintain database logic inside the database itself.
- Checks. You can write checks in the database schema, but they always return TRUE; so they are of no use.
- Foreign keys. You can write them down, but MySQL ignores them.
- Need I go on?

Some things (like checks) can be enabled by using a different table type (InnoDB). But this comes at a price: loss of speed. In fact, if you use InnoDB, you might as well use PostgreSQL or Sybase (or whatever), because both systems are more robust, and also faster in that case.

In short, if you have a database that consists of only one table; or of tables that aren't connected in any way, MySQL is fine. However, such a database doesn't exist...

Vincent

[datune]

Right, voostind, i get the feeling that youre taking things a bit too personal, chill down it's only a discussion, after all that's what makes it interesting

Look, even MattR has agreed to the fact (in another post, but nonetheless he did, that MySQL will be sufficient enough for small/medium loads. (that's his words im using, cause i don't have the experience that he has with DBMS, you can read up on the post http://sitepointforums.com/showthrea...5&pagenumber=2 if you want.)

However, here comes you, saying that MySQL is of no good.
Come one voostind, don't you think that you're excaggerating a tiny bit?

No he wouldn't. A 'real' RDBMS developer adds triggers and functions to the database schema to make sure the database logic is preserverd, INSIDE the database. But then, MySQL doesn't support triggers. Or functions. Or anything a proper DBMS should support.

Wrong, again.

You see, a good developer will ALWAYS use what is best and appropiate for your application.
Any application being seriously developed will have a "Product requirement specifications" booklet, and the RDBMS Developer will use that to judge which DBMS to use.
At least that's how i have experienced it sofar.

And you know, sofar i have never met any developer in the real world saying "Oh no, MySQL is crap, you should only use "put here what you want", etc...". You do that in the real world and people will think of you as being unproffesional.

Oh sure, doing an article database with automated tracking of stock for a huge supermarket with 150 shops divided all over the place you're not gonna use MySQL, but that's not the point is it.

Actually, addslashes is (just like PASSWORD) MySQL-specific and non-ANSI. I know of one other DBMS that supports it (PostgreSQL), but that doesn't make it right. The proper thing to do is to replace single quotes with two single quotes:


PHP:--------------------------------------------------------------------------------
$new = str_replace("'", "''", $old);

--------------------------------------------------------------------------------


That is ANSI, so that is what you should use.

That's some very interresting information, and i admit it is the first time ever i hear such an approach, and if it is going to work, im sure going to use it.

It sucks. It's not a solution to any problem; it's only working around your own incompetence: "I don't know ho secure my DBMS properly, so if I just give my tables weird names, the problem will go away by itself." Yeah right.

It's actually just an added security bonus, nothing else matey. If you think about it you will agree (unless youre being stubborn of course).
I never said it is an solution for everything, but it's nice to use.
Oh, and you can be quite sure that with a real hacker you don't stand a chance, he is not going to try and delete one table of yours...he's gonna delete your whole application.

Have a nice day!
datune

[timnz]

Thank you voostind for your answer(s)!

I'm still in the mindset that I don't "need" those things just yet but it is nonetheless helpful to know all those features of databases in relation to databases as a whole.

The big issue for me is that it is much easier to get hosting with MySQL databases (and cheaper) than it is to get hosting with other database support. I do envisage switching to a real RDBMS in the future however it is not a priority at this point in time because MySQL does the job I want it to. - although I can see how MySQL has plenty of room for improvement.

Datune don't tell voostind he's wrong just because you don't agree with him. What you see as a good developer/database user and what he sees as one is a subjective point.

[Anonymous]

Great article.
Thanks!

[firepages]

In defense of Datatune all he said was that tdarlings example was wrong/crap and it was , that particular attack will not work in MySQL fullstop whatever variances you apply.

That MySQL is vunerable to SQL injection is neither new or interesting , though its good to keep the subject popping up now and again.

That MySQL is not a true RDBMS is neither new nor interesting , that it ably powers millions of sites on ridiculously overloaded shared servers is a fact you can ignore at leisure.

I hear time and again from peeps in these forums 'use the right tool for the job' , MySQL is the right tool for many many jobs , & just because someone uses it does not diminish thier IQ.

I have just started playing with sqlite and (even though it supports triggers & views etc it is obviously not an RDBMS , its a glorified flatfile , but it too has its uses in specific places.

Would you have us all use PostgreSQL or Oracle if we are not to be branded something 'orrible

when posgreSQL finally gets x-platform then it may be useful to the masses (I started my web-development on postgreSQL & eek ! mSQL) as I see WAMP users get laughed out the court as well...

beginning to sound a bit l33t to me ...

[Anonymous]

The examples provided are useful but it's not practical to think that any developer of a big corporate site would still do stuff like controlling username/passwords and related user rights in the database instead of the page logic. If a developer cannot think that this is problematic while creating a connection in the first hand, should resign.

[Anonymous]

Good and very informative article, I had never heard about it before. Thanks for the informationa and samples

[lieut_data]

Just an interesting note, in regards to the use of the PASSWORD function:

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.

Anyone you know that *is* using the password function should stop:

Note: The PASSWORD() function is used by the authentication system in MySQL Server, you should NOT use it in your own applications. For that purpose, use MD5() or SHA1() instead. Also see RFC-2195 for more information about handling passwords and authentication securely in your application.

(See http://www.mysql.com/doc/en/Miscella...functions.html)


The only valid use for PASSWORD would be in a program like PHPMyAdmin, to manage MySQL

[tfountain]

The manual doesn't actually say why you shouldn't use PASSWORD() in your own applications. Is there any reason apart from the lack of portability?

[MattR]

It doesn't look like it uses MD5, so it isn't as secure, either. In short, there's no good reason to use it, so don't.

[Amit]

Actually, to take a few steps back. I would advise not allowing the special charaters (like ;,<> etc) in the username would actually prevent (in certain cases) the sql attack

You can use something like this in JavaScript

function IsValid( oField )
{
	if (oField.name=='username')
	{
		re = /^(([a-z])+([a-z]|[0-9]|\_)*)*$/i;
		$sMsg = "Username is invalid.\nUsername should contain only alphabets\nnumericals(0-9) and underscore(_)\nand should start with an alphabet."
	}
	if ( !re.test(oField.value) )
	{
		alert( $sMsg );
		oField.value = '';
		oField.focus();
		return false;
	}
}

And while using in the form ....

<input class='tb' type='text' name='username' onblur='IsValid(this);' maxlength='20' />

And then again we have to check while processing the form (because if JavaScript is turned off, the above example would fail)

$sErrMsg .= ( empty($username) ? "Username is empty.\n" : '' )
    . ( !preg_match('/^(([a-z])+([a-z]|[0-9]|\_)*)*$/i', $username) ? "Username is not valid.\n" : '' ); 

and using the $sErrMsg later on ...

if (!empty($sErrMsg))
{
   echo nl2br($sErrMsg);
} 

Also, if you want to restrict the username's character you can use the regular expression like this ....
'/^(([a-z])+([a-z]|[0-9]|\_){4,19})?$/i'

This would actually restrict the username from 5 to 20 characters.

Similarly can be used for the other fields like password, etc ...

Hope this helps ...

[HogMan]

$new = str_replace("'", "''", $old);

Does anyone use this instead of addslashes? From the php manual, addslashes does the following

string addslashes(string str)
Returns a string with backslashes before characters that need to be quoted in database queries etc. These characters are single quote ('), double quote ("), backslash (\) and NUL (the NULL byte).

So, if not using addslashes with MySQL and just replacing the ' with '' (2 single quotes), won't that cause an issue if the query contains a " or \

Some code that uses addslashes and str_replace on a name

#!/usr/bin/php -q
<?php
$name = "test'sare\\\"good";
$slashname = addslashes($name);
$othername = str_replace("'", "''", $name);
echo "INSERT INTO foo (name) VALUES ('$slashname');\n";
echo "INSERT INTO foo (name) VALUES ('$othername');\n";
?>

Result queries

INSERT INTO foo (name) VALUES ('test\'sare\\\"good');
INSERT INTO foo (name) VALUES ('test''sare\"good');

Now, checking whats in the db

mysql> SELECT name FROM foo;
+-----------------+
| name            |
+-----------------+
| test'sare\"good |
| test'sare"good  |
+-----------------+

With just replacing ' with '', won't there be an issue now? I would need to slash my string after getting it from the db where before I wouldn't? Sorry if I misunderstood this.

[MattR]

The MySQL method of escaping strings (with the backslash) is non-standard. The SQL standard requires strings to be enclosed in single quotes (e.g. WHERE username = 'Bob Ross') and escaped with double-single quotes (e.g. WHERE store_name = 'Bob''s Bait Shop').

[Amit]

I would advise using mysql_escape_string() if want to escape the string for MySQL data.

[HogMan]

The MySQL method of escaping strings (with the backslash) is non-standard. The SQL standard requires strings to be enclosed in single quotes (e.g. WHERE username = 'Bob Ross') and escaped with double-single quotes (e.g. WHERE store_name = 'Bob''s Bait Shop').

Sorry, I'm confused (which is common for me ). So to follow the standard and not use addslashes, wouldn't we still need to worry about \ and " so they are escaped properly?

[MattR]

If you use the string replace function as provided (and follow the standard for strings) everything will work fine in most DBMSs. I don't know about MySQL.

[wwb_99]

One thing I have not seen mentioned here: Permissions.

Alot of these issue can be handled very efficently by making sure your webuser db account cannot do things they dont need to do. Like DROP tables.

When I work with SQL server my web user can't even access tables directly. Instead I make stored procedures and views as needed to allow them to read and update data.

WWB