PHP and MySQL Basics IV -- SQL Injection and Prepared Statements

SQL Injection is a highly feared and often misunderstood problem. The basic phobia is that someone hijacks your SQL request and suddenly has full access to everything in your database. Well, it usually is not that easy and it is actually easy to avoid.

Rule 1: Never Trust User Supplied

The usual example is something like a query SELECT * FROM customer_data WHERE customer_id='$id' and the programmer was expecting an integer for the customer_id. But a dastardly use inserts some horrible SQL code to pirate the information so the query looks like SELECT * FROM customer_data WHERE customer_id=1 OR customer_id > 0 and suddenly all your customer data is out free in the universe waiting for who knows what.

The code could have checked to see if the value of customer_id was truly an integer or returning an error if not. The is_int function was designed to do just this.

if is_int($customer_id)  {
  //Do all the stuff we want to do if we have a integer
  //submitted for a customer_id
} else {
  echo "Hey! I want an INTEGER for a customer identification number!";
}

Even more dastardly and from the PHP Manual where the dastardly injector resets all the passwords:


<?php
But a malicious user subits the value ' or uid like'%admin% to $uid to change the admin's password, or simply 
sets $pwd to hehehe', trusted=100, admin='yes to gain more privileges. Then, the query will be twisted:
>?php

// $uid: ' or uid like '%admin%
$query = "UPDATE usertable SET pwd='...' WHERE uid='' or uid like '%admin%';"
// $pwd: hehehe', trusted=100, admin='yes
$query = "UPDATE usertable SET pwd='hehehe', trusted=100, admin='yes' WHERE
...;";?>

Paranoid yet? This is another case checking the values for reasonableness can save grief. Is the uid an integer, is that integer in a proper range for uids? If too low or too high, you need to suspect someone is doing something bad.

On the database side

One thing I have recommended and implemented for years is separate users for SELECTs and INSERT/UPDATE/DELETEs. It is too easy to use one connection string over and over. But if you are in a situation where you need to take 'loose' information from an user and use that information against you data base, you need to split the queries. Set up a cust_read account on the MySQL server that can read customer data ONLY. The a corresponding cust_mod for all the other queries. This way you are assured that they can not delete or update records with that account if things get compromised. Also do not user superuser accounts like root for production database interactions -- save these accounts for maintenance functions.

Bound variables

The MySQL server supports using anonymous, positional placeholder with ?. Again from the PHP Manual:


/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {
    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

Or as used in a SELECT


/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("SELECT * FROM test WHERE id = ?"))) {
    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
/* Prepared statement, stage 2: bind and execute */
$id = 1;
if (!$stmt->bind_param("i", $id)) {
    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}

if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
} else {
    echo "Got it!!!\n";
}

Prepared Statements - Help But Not A Cure

Prepared statements make it nearly impossible to cram lots of data into one variable. That was nearly impossible.

Take the extra time to make sure that integers are really integers and that they are in the correct range of numbers. If you are looking for a string of up to say 30 characters that you do something when the length is over run. MySQL used to get a lot of flack with people running servers in less that strict mode and having excess data truncated with only a warning generated. Now sometimes that data is valuable. But if your corporate standard is to store emails in 45 characters and the user is trying to store 50, you need to programmatically warn those with long emails that their data is too long (and the Ops folks that the email to the account if going to bounce (if you do accept that truncated email)).

RTFM

The PHP Manual's section on SQL Injection is a must read. Do use separate MySQL accounts for SELECTs and INSERT/UPDATE/DELETEs. Keep asking yourself how to keep from exposing more data than the absolute minimum needed. Do not use SELECT * FROM foo but instead explicitly name the columns in your select statement; If something goes wrong you are not exposing data column that may have private information. And be paranoid -- keep asking if there is something else you can do to protect the data.