mysql - How can I prevent SQL injection in PHP? -

if user input inserted without modification sql query, application becomes vulnerable sql injection, in following example:

$unsafe_variable = $_post['user_input'];   mysql_query("insert `table` (`column`) values ('$unsafe_variable')"); 

that's because user can input value'); drop table table;--, , query becomes:

insert `table` (`column`) values('value'); drop table table;--') 

what can done prevent happening?

use prepared statements , parameterized queries. these sql statements sent , parsed database server separately parameters. way impossible attacker inject malicious sql.

you have 2 options achieve this:

1. using pdo (for supported database driver):

   $stmt = $pdo->prepare('select * employees name = :name');  $stmt->execute(array('name' => $name));  foreach ($stmt $row) {     // $row } 

2. using mysqli (for mysql):

   $stmt = $dbconnection->prepare('select * employees name = ?'); $stmt->bind_param('s', $name);  $stmt->execute();  $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) {     // $row } 

if you're connecting database other mysql, there driver-specific second option can refer (e.g. pg_prepare() , pg_execute() postgresql). pdo universal option.

correctly setting connection

note when using pdo access mysql database real prepared statements not used default. fix have disable emulation of prepared statements. example of creating connection using pdo is:

$dbconnection = new pdo('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');  $dbconnection->setattribute(pdo::attr_emulate_prepares, false); $dbconnection->setattribute(pdo::attr_errmode, pdo::errmode_exception); 

in above example error mode isn't strictly necessary, but advised add it. way script not stop fatal error when goes wrong. , gives developer chance catch error(s) thrown pdoexceptions.

what mandatory first setattribute() line, tells pdo disable emulated prepared statements , use real prepared statements. makes sure statement , values aren't parsed php before sending mysql server (giving possible attacker no chance inject malicious sql).

although can set charset in options of constructor, it's important note 'older' versions of php (< 5.3.6) silently ignored charset parameter in dsn.

explanation

what happens sql statement pass prepare parsed , compiled database server. specifying parameters (either ? or named parameter :name in example above) tell database engine want filter on. when call execute, prepared statement combined parameter values specify.

the important thing here parameter values combined compiled statement, not sql string. sql injection works tricking script including malicious strings when creates sql send database. sending actual sql separately parameters, limit risk of ending didn't intend. parameters send when using prepared statement treated strings (although database engine may optimization parameters may end numbers too, of course). in example above, if $name variable contains 'sarah'; delete employees result search string "'sarah'; delete employees", , not end an empty table.

another benefit using prepared statements if execute same statement many times in same session parsed , compiled once, giving speed gains.

oh, , since asked how insert, here's example (using pdo):

$preparedstatement = $db->prepare('insert table (column) values (:column)');  $preparedstatement->execute(array('column' => $unsafevalue)); 

can prepared statements used dynamic queries?

while can still use prepared statements query parameters, structure of dynamic query cannot parametrized , query features cannot parametrized.

for these specific scenarios, best thing use whitelist filter restricts possible values.

// value whitelist // $dir can 'desc' otherwise 'asc' if (empty($dir) || $dir !== 'desc') {    $dir = 'asc'; }