Close

prepared statements

Prevent SQL injection attacks with prepared statements

Bit of a hot topic lately in my inbox.

Here’s what an old, vulnerable query might look like:

$expected_data = 1;
$query = “SELECT * FROM users where id=$expected_data”;
$result = $mysqli->query($query);

The problem here is we’re injecting user-submitted data directly into our SQL statement without any sort of escaping or validation. So, a hacker could enter something like this in our form:

1; DROP TABLE users;

Changing our full query to:

SELECT * FROM users where id=1; DROP TABLE users;

Which, as you can probably see, will execute the SELECT statement but then drop our users table. No bueno. And, that’s a simple example. SQL injection attacks can be used to do all sorts of things: getting passwords, gaining privileges, making superusers… and all sorts of stuff.

Luckily, there’s an easy way to prevent this class of SQL injection:

Prepared statements.

Prepared statements split the query from the data so that the data submitted can’t be used to alter how the query is run; thus preventing injection attacks. Here’s an example of how our code would change:

$expected_data = 1;
$stmt = $mysqli->prepare(“SELECT * FROM users where id=?”);
$stmt->bind_param(“d”, $expected_data);
$stmt->execute();
$result = $stmt->get_result();

Notice how we separated the data from the query. We send the query to the server first and then we bind the data to that. This prevents the submitted data from altering the query and letting the hacker in.

This is how you should write your queries.

Now, if this stuff is new to you or you don’t feel like you quite fully get it yet, then I recommend diving into it. One good way to learn everything PHP is with this tutorial. You’ll learn prepared statements… but also OOP, all of MySQL with PHP, $_GET, $_POST, working with files, folders, if/else/switch… all of it. Plus, you build several¬†fully-functioning scripts so you learn how to put it all together and build something real.

No brainer, IMNSHO: http://www.johnmorrisonline.com/php

Later,

John “Don’t Hack Me Bro” Morris

January 29, 2017

A Simple PHP Class For Prepared Statements in MySQLi

PHP Prepared Statements Class

Prepared statements are all the rage right now in PHP development… and for good reason. Not only do prepared statements make your queries more secure… they also help future-proof your code by relying more heavily on PHP itself for that security.

If you’re not using prepared statements in your queries, you really should be. Here’s a simple class that helps you do just that using MySQLi:

<?php
if ( !class_exists( 'DB' ) ) {
class DB {
public function __construct($user, $password, $database, $host = 'localhost') {
$this->user = $user;
$this->password = $password;
$this->database = $database;
$this->host = $host;
}
protected function connect() {
return new mysqli($this->host, $this->user, $this->password, $this->database);
}
public function query($query) {
$db = $this->connect();
$result = $db->query($query);

while ( $row = $result->fetch_object() ) {
$results[] = $row;
}

return $results;
}
public function insert($table, $data, $format) {
// Check for $table or $data not set
if ( empty( $table ) || empty( $data ) ) {
return false;
}

// Connect to the database
$db = $this->connect();

// Cast $data and $format to arrays
$data = (array) $data;
$format = (array) $format;

// Build format string
$format = implode('', $format);
$format = str_replace('%', '', $format);

list( $fields, $placeholders, $values ) = $this->prep_query($data);

// Prepend $format onto $values
array_unshift($values, $format);

// Prepary our query for binding
$stmt = $db->prepare("INSERT INTO {$table} ({$fields}) VALUES ({$placeholders})");

// Dynamically bind values
call_user_func_array( array( $stmt, 'bind_param'), $this->ref_values($values));

// Execute the query
$stmt->execute();

// Check for successful insertion
if ( $stmt->affected_rows ) {
return true;
}

return false;
}
public function update($table, $data, $format, $where, $where_format) {
// Check for $table or $data not set
if ( empty( $table ) || empty( $data ) ) {
return false;
}

// Connect to the database
$db = $this->connect();

// Cast $data and $format to arrays
$data = (array) $data;
$format = (array) $format;

// Build format array
$format = implode('', $format);
$format = str_replace('%', '', $format);
$where_format = implode('', $where_format);
$where_format = str_replace('%', '', $where_format);
$format .= $where_format;

list( $fields, $placeholders, $values ) = $this->prep_query($data, 'update');

//Format where clause
$where_clause = '';
$where_values = '';
$count = 0;

foreach ( $where as $field => $value ) {
if ( $count > 0 ) {
$where_clause .= ' AND ';
}

$where_clause .= $field . '=?';
$where_values[] = $value;

$count++;
}

// Prepend $format onto $values
array_unshift($values, $format);
$values = array_merge($values, $where_values);

// Prepary our query for binding
$stmt = $db->prepare("UPDATE {$table} SET {$placeholders} WHERE {$where_clause}");

// Dynamically bind values
call_user_func_array( array( $stmt, 'bind_param'), $this->ref_values($values));

// Execute the query
$stmt->execute();

// Check for successful insertion
if ( $stmt->affected_rows ) {
return true;
}

return false;
}
public function select($query, $data, $format) {
// Connect to the database
$db = $this->connect();

//Prepare our query for binding
$stmt = $db->prepare($query);

//Normalize format
$format = implode('', $format);
$format = str_replace('%', '', $format);

// Prepend $format onto $values
array_unshift($data, $format);

//Dynamically bind values
call_user_func_array( array( $stmt, 'bind_param'), $this->ref_values($data));

//Execute the query
$stmt->execute();

//Fetch results
$result = $stmt->get_result();

//Create results object
while ($row = $result->fetch_object()) {
$results[] = $row;
}

return $results;
}
public function delete($table, $id) {
// Connect to the database
$db = $this->connect();

// Prepary our query for binding
$stmt = $db->prepare("DELETE FROM {$table} WHERE ID = ?");

// Dynamically bind values
$stmt->bind_param('d', $id);

// Execute the query
$stmt->execute();

// Check for successful insertion
if ( $stmt->affected_rows ) {
return true;
}
}
private function prep_query($data, $type='insert') {
// Instantiate $fields and $placeholders for looping
$fields = '';
$placeholders = '';
$values = array();

// Loop through $data and build $fields, $placeholders, and $values
foreach ( $data as $field => $value ) {
$fields .= "{$field},";
$values[] = $value;

if ( $type == 'update') {
$placeholders .= $field . '=?,';
} else {
$placeholders .= '?,';
}

}

// Normalize $fields and $placeholders for inserting
$fields = substr($fields, 0, -1);
$placeholders = substr($placeholders, 0, -1);

return array( $fields, $placeholders, $values );
}
private function ref_values($array) {
$refs = array();

foreach ($array as $key => $value) {
$refs[$key] = &$array[$key];
}

return $refs;
}
}
}

$db = new DB('root', '', 'test');
print_r($db->select('SELECT * FROM objects WHERE ID = ?', array(10), array('%d')));

I recommend walking through this code and unraveling how it all comes together. There are a few gotchas when using prepared statements in a dynamic way like this.

Or you could just check out my course PHP & MySQL 101 where I walk you step-by-step through building this exact class (and a lot more).

December 26, 2013

Should I Use mysqli_real_escape_string With Prepared Statements in PHP?

Graham recently asked me:

Do I still need to used mysqli_real_escape_string when used prepared statements in PHP?

The simple answer is no.

The way it used to work is that you would take form input data, put that into a variable, and inject that data into your MySQL query in order to add that data to the database.

Now, a big problem with that is SQL Injection attacks where a hacker could inject SQL code into your query and perform actions on your database… which is something you definitely don’t want.

So, the standard solution became using mysql_real_escape_string to sanitize data before sending to the database.

Of course, that’s not the preferred solution anymore. Prepared statements are:

But, with PHP5, the PHP developers built an entire class into PHP for working MySQL. With that class, there are now prepared statements in PHP… and prepared statements allow you to “bind” data to a query using sprintf-like syntax… rather than “inject” your data into those queries.

And, with this new system, the methods that bind the data to your query do the sanitizing for you. So, mysqli_real_escape_string is no longer necessary WHEN you bind values this way.

Of course, if you have some other way you’re injecting input data into your queries, you still need to sanitize that data… and mysqli_real_escape_string is still the main method for doing that.

June 6, 2013