PDO vs MysQLi Security using Prepared Statements

Both PDO and MySQLi provide support for prepared queries. This helps preventing SQL injection security issues, as long as you only use prepared queries to insert dynamic parameters in the queries.

For example, consider a hacker that tries to inject malicious SQL passing a forged value to the parameter code of a HTTP POST request that could be emulated like this:

$_POST['code'] = "'; DELETE FROM products; /*";

If you do not escape this value, it will be inserted in query as is, and so it would delete all rows from the products table.

One way to make queries more secure avoiding SQL injection is to quote string parameter values to insert escape characters.

// PDO, “manual” escaping
$name = PDO::quote( $_POST['code'] );
$pdo->query( "SELECT id, name FROM products WHERE code = $code" );
// mysqli, “manual” escaping
$name = mysqli_real_escape_string( $_POST['code'] );
$mysqli->query( "SELECT id, name FROM products WHERE name = '$code'" );

PDO::quote() not only escapes the string, but it also adds quotes. mysqli_real_escape_string() will only escape the string, so you will need to add the quotes manually.

// PDO, prepared statement
$pdo->prepare( 'SELECT id, name FROM products WHERE code = :code' );
$pdo->execute( array( ':code' => $_POST['code'] ) );
// mysqli, prepared statements
$query = $mysqli->prepare('SELECT id, name FROM users WHERE code = ?');
$query->bind_param('s', $_POST['code']);
$query->execute();

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

WordPress.com.

Up ↑

%d bloggers like this: