Preventing SQL Injection Attacks

In this posting, I will show how to use PHP prepared statements to prevent SQL injection attacks when using a MySQL database.  An SQL injection attack, as the name implies, is an attempt to insert malicious code into a site's SQL queries.  One aim of such attacks is to create a syntactically invalid query, thereby revealing something about the script or database in the resulting error message.  An even bigger goal would be to alter, destroy, or expose your stored data.

To prevent such an attack, you should validate all data to be used in queries and perform typecasting whenever possible.  In addition, you can use a PHP function like mysqli_real_escape_string(), which makes the data safe to use. in queries.

As an alternative is to use Prepared Statements which were introduced to MySQL in version 4.1 and available to PHP in version 5 through the Improved MySQL extension.  Without prepared statements, the entire query, including the SQL syntax and the specific values, is sent to the MySQL parser and executed as one long string.  With a prepared query, the SQL syntax is sent to the MySQL parser first.  Then the specific values are sent separately.  MySQL then assembles the query using those values and then executes it.

Prepared statements can be created out of any  SELECT, INSERT, UPDATE,  or DELECT query.  Start by structuring your query, marking placeholders using question marks. An example is shown below:

   Take a query such as:
   $q = "SELECT first_name, last_name, email FROM customers WHERE customer_id=$cust_id";
   As a prepared statement, this query becomes:
   $q= "SELECT first_name, last_name, email FROM customers WHERE customer_id=?";
   Next, prepare the statement in MySQL:
   $stmt=mysqli_prepare($dbconnection, $q);
   At this point, MySQL will parse the query for valid syntax, but won't execute it.
   Next, bind the PHP variables to the query's placeholders:
   mysqli_stmt_bind_param($stmt, 'i', $cust_id);
   The i part of the command indicates what kind of variable, integer in this case, to expect.

The table below shows the characters to use when binding variables:

Letter	Represents
d	Decimal
i	Integer
b	Blog(binary data)
s	All Other Types

Prepared statements will always be more secure than running queries the traditional way.  In addition, they may also be faster.  If a PHP script sends the same query to MySQL multiple times, using different values each time, prepared statements could speed things up.  In this situation, the query itself is only parsed by MySQL once.  Then the values are sent to MySQL separately.