Newbies Beware! Not so good advice...
Thanks to Mike for sending in this WTF on a tutorial about PHP and MySQL security. Every PHP developer should know about the hazards of SQL injection. In fact it is important enough to be an offical part of the PHP documentation.
Perhaps the author who wrote this tutorial should have read the manual before declaring that PHP and MySQL doesn't allow SQL injection vulnerabilities any more and providing some pretty moot examples. Here is an excerpt from the article that describes how SQL Injections can happen.
How could hackers manipulate the form values to drop the whole database? Well it's pretty simple they just add a semicolon (which ends a query) then add the SQL statement ;DROP DATABASE mysql , or any other query they want.
Here's the provided code example:
<?php
$query = "INSERT INTO user (host, user, password, select_priv, insert_priv, update_ priv) VALUES ('localhost', 'someuser;drop database mysql;', PASSWORD('$password'), 'Y', 'Y', 'Y')";
?>
Ack! Can that be any more wrong? First mysql_query() will only run one query at a time. Even if an attacker managed to inject an unexpected semicolon, the worst that could happen is the query would fail and confuse an application without adequate error checking. Semicolons more a part of mysql clients rather than actual SQL syntax. I don't think semicolon injection is a major security problem. If an application doesn't have adequate error handling it might fail but other than that there's really no security hazards to the database.
Secondly 'someuser;dropdatabase mysql;' would be interpreted as a string and there is no security problem there. The semicolons will have no effect other than creating a pretty ugly username.
SQL injection vulnerabilities usually occur from lazy coding. All data going into a database should be totally checked and cleaned. Writting data checking code is pretty tedious which is why it is often skipped. Here are some guidelines to follow for accepting data from users for the database:
- Don't trust any data that comes from the user ment for the database.
- Be especially paranoid about database ID's submitted throwugh $_GET because these can be easily modified.
- Heck, be especially paranoid about any database data submitted through $_GET!
- Run all string data through
addslashes() - Make sure integers are actually integers, floats are floats, etc. Reformat them, explicitly cast them to the proper types, do the minimum necessary to ensure clean data for the database.
- Make sure you have business rules for your data.
- Check data validity before it goes into the database. Make sure it conforms to a proper set of business rules.
