PHP/SQL Database querying good practice and security

Question

So I'm a slightly seasoned php developer and have been 'doin the damn thing' since 2007; however, I am still relatively n00bish when it comes to securing my applications. In the way that I don't really know everything I know I could and should.

I have picked up Securing PHP Web Applications and am reading my way through it testing things out along the way. I have some questions for the general SO group that relate to database querying (mainly under mysql):

When creating apps that put data to a database is mysql_real_escape_string and general checking (is_numeric etc) on input data enough? What about other types of attacks different from sql injection.

Could someone explain stored procedures and prepared statements with a bit more info than - you make them and make calls to them. I would like to know how they work, what validation goes on behind the scenes.

I work in a php4 bound environment and php5 is not an option for the time being. Has anyone else been in this position before, what did you do to secure your applications while all the cool kids are using that sweet new mysqli interface?

What are some general good practices people have found to be advantageous, emphasis on creating an infrastructure capable of withstanding upgrades and possible migrations (like moving php4 to php5).

Note: have had a search around couldn't find anything similar to this that hit the php-mysql security.

Answer 1

Javier's answer which has the owasp link is a good start.

There are a few more things you can do more:

1. Regarding SQL injection attacks, you can write a function that will remove common SQL statements from the input like " DROP " or "DELETE * WHERE", like this:

   *$sqlarray = array( " DROP ","or 1=1","union select","SELECT * FROM","select host","create table","FROM users","users WHERE");*

   Then write the function that will check your input against this array. Make sure any of the stuff inside the $sqlarray won't be common input from your users. (Don't forget to use strtolower on this, thanks lou).

2. I'm not sure if memcache works with PHP 4 but you can put in place some spam protection with memcache by only allowing a certain remote IP access to the process.php page X amount of times in Y time period.

3. Privileges is important. If you only need insert privileges (say, order processing), then you should log into the database on the order process page with a user that only has insert and maybe select privileges. This means that even if a SQL injection got through, they could only perform INSERT / SELECT queries and not delete or restructuring.

4. Put important php processing files in a directory such as /include. Then disallow all IPs access to that /include directory.

5. Put a salted MD5 with the user's agent + remoteip + your salt in the user's session, and make it verify on every page load that the correct MD5 is in their cookie.

6. Disallow certain headers (http://www.owasp.org/index.php/Testing_for_HTTP_Methods_and_XST) . Disallow PUT(If you dont need file uploads)/TRACE/CONNECT/DELETE headers.

Answer 2

My recommendations:

1. ditch mysqli in favor of PDO (with mysql driver)
2. use PDO paremeterized prepared statements

You can then do something like:

$pdo_obj = new PDO( 'mysql:server=localhost; dbname=mydatabase', 
                    $dbusername, $dbpassword );

$sql = 'SELECT column FROM table WHERE condition=:condition';
$params = array( ':condition' => 1 );

$statement = $pdo_obj->prepare( $sql, 
    array( PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY ) );
$statement->execute( $params );
$result = $statement->fetchAll( PDO::FETCH_ASSOC );

PROs:

1. No more manual escaping since PDO does it all for you!
2. It's relatively easy to switch database backends all of a sudden.

CONs:

• i cannot think of any.