How do I prevent sql injection with php and mysql

Question

I have a form into which the visitor can enter data, and I want to store this data in a mysql database via the $_POST variable. What do I need to prevent sql injection?

Answer 1

Have a read at this, and next time do some searching:

http://stackoverflow.com/questions/1973/what-is-the-best-way-to-avoid-sql-injection-attacks

Answer 2

Use prepared statements.

Answer 3

You have to follow some rules while adding any data to the query, no matter from where it come - from user or form or anything. The rules always remain the same.

To send a query to the database, you have 2 options:

1. Build a query usual way, to make it look exactly as SQL query you can run in sql console.
   To do it, one should understand a whole set of rules, not just "use mysql_real_escape_string".
   Rules such as:

   • strings should be both enclosed in quotes and escaped. that's the only meaning of escaping: it's just easacpe delimiters! (and some other characters - string termination char and escape character itself). Without surrounding quotes mysql_real_escape_string is just useless.
   • numbers should be cast to it's type explicitly. Though while data numbers can be threaten just like strings, there are some numbers, like LIMIT clause parameters, which cannot be escaped and can be only cast.

2. To send query and data separately.
   This is most preferred way as it can be shortened to just "use binding". All strings, numbers and LIMIT parameters can be bound - no worry at all.
   Using this method, your query with placeholders being sent to database as is, and bound data being sent in separate packets, so, it cannot interfere. It is just like code and data separation. You send your program (query itself) separated from the data.

Everything said above covers only data insertion. But sometimes we have to make our query even more dynamic, adding operators or identifiers.
In this case every dynamic parameter should be hardcoded in our script and chosen from that set.
For example, to do dynamic ordering:

$orders  = array("name","price","qty");
$key     = array_search($_GET['sort'],$orders));
$orderby = $orders[$key];
$query   = "SELECT * FROM `table` ORDER BY $orderby";

or dynamic search:

$w     = array();
$where = '';

if (!empty($_GET['rooms']))     $w[]="rooms='".mesc($_GET['rooms'])."'";
if (!empty($_GET['space']))     $w[]="space='".mesc($_GET['space'])."'";
if (!empty($_GET['max_price'])) $w[]="price < '".mesc($_GET['max_price'])."'";

if (count($w)) $where="WHERE ".implode(' AND ',$w);
$query="select * from table $where";

in this example we're adding to the query only data entered by user, not field names, which are all hardcoded in the script. For the binding the algorithm is very similar

And so on.

Answer 4

I gave a presentation at the PHP TEK-X conference in May 2010 about this topic, and I tried to cover multiple methods for defending against SQL Injection. There is no single method that is best in all cases, so you should learn multiple methods and use all of them:

• Validate user input or any other content from external sources (even data from within your own database) before interpolating it into an SQL query. You can use PHP's filter extension or regular expressions, for instance.

• Force external content to be in correct format. For example, (int) $_POST["userid"] typecasts that content to be a plain integer, so it's safe to use.

• When including dynamic content in place of literal values in SQL expressions, use prepared queries with parameters. Note that the plain mysql extension in PHP does not support query parameters -- use PDO. I don't use mysqli because its API is inconsistent and hard to use.

• When using the IN() predicate, you can't use one parameter for a list of values. Concatenate multiple parameter placeholders, as many as you have values in your list. This is not hard, it only take a line or two of code:

  $sql = "SELECT ... FROM ... WHERE user_id IN ("
      . join(",", array_fill(0,count($userid_list),"?")) . ")";
  $pdoStmt = $pdo->prepare($sql);
  $pdoStmt->execute($userid_list);
  

• When using dynamic table names, column names, or SQL keywords, you can't use query parameters. You have to interpolate dynamic content. But you can use whitelisting techniques to map the untrusted content to legal, safe identifiers and keywords.

See my presentation SQL Injection Myths and Fallacies for more information and examples.

Also you might like my new book SQL Antipatterns: Avoiding the Pitfalls of Database Programming. My book has a chapter about SQL Injection.