Use MySQL prepared Statements in PHP for more security

Hint: In this article I use PHP to demonstrate prepared statements, but the concept works with any other language as well.

Dynamic websites have one problem: They are open books to attackers. You don’t need to be a security expert, but if you miss out about the basics, your application will get hacked fast. And it all starts with dynamic input provided by your users. Remember “Greet yourself from PHP”?

Using dynamic user input comes with several risks. In this article you’ll see how you can increase the security of your application significantly with very low effort when using these values in mysql queries.

The environment / setup #

For this article, I’m using PHP 8.4 with latest MySQL 9.5.

I use a database with just one table:

1
2
3
4
5

CREATE TABLE users
(
    id       INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE
);

I inserted three rows into this table:

1
2
3
4

INSERT INTO users (username)
VALUES ("Oliver"),
       ("Tris"),
       ("Golo");

Understanding SQL injection #

I strongly believe once you understand something, it’s more easy to work on a solution. So let’s start here: Understanding the actual risk of SQL injection attacks. Have a look on this two SQL queries:

1
2
3
4
5

-- OK
SELECT * from users where username="Oliver";
    
-- OK?
SELECT * from users where username="" OR 1=1 -- ";

The first one returns the expected result. The second query will return all entries from the users table. But you wouldn’t put this query to your code, right… right?

Probably you did already… Let’s come up with some PHP to find a users by it’s name:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

<?php
    $db = new mysqli(
        'localhost',
        'root',
        'root',
        'my_app',
        3306
    );
    
    $username = 'Oliver';
    $data     = $db->query(
        'SELECT * from users where username="' . $username . '";'
    );

    var_dump($data->fetch_all());
    die();

The output should reflect our user that we’re looking for. So far so good. Now, let’s make it more generic and load $username from a query parameter in the URL.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

<?php
    $db = new mysqli(
        'localhost',
        'root',
        'root',
        'my_app',
        3306
    );
    
    $username = $_GET['username'];
    $data     = $db->query(
        'SELECT * from users where username="' . $username . '";'
    );

    var_dump($data->fetch_all());
    die();

To test this script, you can use PHP to host a temporary, local webserver for you by simply running this command:

1

php -S localhost:8080 -t "some_folder"

Replace some_folder with the absolute path to the folder where you store the PHP script. Now just open http://localhost:8080/my_script.php?username=Oliver and see the result.

1
2
3
4
5

array (size=1)
  0 => 
    array (size=2)
      0 => string '1' (length=1)
      1 => string 'Oliver' (length=6)

(Query: SELECT * from users where username="Oliver")

To run the attack, instead of Oliver use " OR 1=1 -- .

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

array (size=3)
  0 => 
    array (size=2)
      0 => string '3' (length=1)
      1 => string 'Golo' (length=4)
  1 => 
    array (size=2)
      0 => string '1' (length=1)
      1 => string 'Oliver' (length=6)
  2 => 
    array (size=2)
      0 => string '2' (length=1)
      1 => string 'Tris' (length=4)

(Query: SELECT * from users where username="" OR 1=1 -- ")

Congratulation, you just ran an SQL injection attack against your code. As you can see, the concept simply is to modify queries towards your database by manipulating user-provided input data.

In production environments, you wouldn’t dump results of a database query to your user, I agree. Still, the concept to change data returned by the database that finally will modify your codes behavior, is applicable.

Apply that idea to the “username” field of your login form, would you be able to login as “admin”?

Use prepared statements to prevent SQL injection #

Making your code save of sql injection attacks is fairly simple. Instead of concatenating a query, you simply use placeholders for all dynamic values. Then you tell PHP what values to use for these placeholders:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

<?php
    $db = new mysqli(
        'localhost',
        'root',
        'root',
        'my_app',
        3306
    );
    
    $username = $_GET['username'];
    $query    = $db->prepare(
        'SELECT * from users where username=?;'
    );
    $query->bind_param('s', $username);
    $query->execute();
    $data = $query->get_result();

    var_dump($data->fetch_all());
    die();

As you can see the query now doesn’t use $username directly, but a ? instead. In a second step, using bind_param, we add the dynamic value to the query. Just after that we execute the query and load the results. This slight change prevents SQL injection attacks.

This is because now the database knows, that whatever is the dynamic value, it will not change the logic of the query (which is what it did in the beforehand example).

If you now try to run the attack again, using " OR 1=1 -- as value for username query parameter, you’ll not get any results (except a user with such name would exist):

1
2

array (size=0)
  empty

Supported types for prepared statements in PHP #

In the example above, you can see bind_param being called with two arguments: s and $username. The s stands for string, which tells MySQL that the dynamic value has to be interpret as a string. There’s multiple types supported:

i

integer - you can use this for any number (without decimals)

d

float - you can use this for any number (with decimals)

s

string - you can use this for any string input

b

blob - use this to provide (large) binary data (e.g. file contents).

Using multiple dynamic inputs #

For sure you can use multiple dynamic inputs within one prepared statement:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

<?php
    $db = new mysqli(
        'localhost',
        'root',
        'root',
        'my_app',
        3306
    );
    
    $username = $_GET['username'];
    $id       = $_GET['id'];
    $query    = $db->prepare(
        'SELECT * from users where username=? and id=?;'
    );
    $query->bind_param('si', $username, $id);
    $query->execute();
    $data = $query->get_result();

    var_dump($data->fetch_all());
    die();

As you can see, now beside username we also lookup id. In bind_param now we specify three arguments:

1. si which specifies the two data types string and int
2. the username
3. the id

Drawbacks of prepared statements #

I like the increased security that comes with prepared statements. But I dislike the “disconnect” of information. Two separate statements in code must follow the same logic without the IDE being able to support me and tell me about any stupidity I’m doing.

In the example above I specify SELECT * from users where username=? and id=?; as my query and just later I call bind_param and need to give in the arguments in the exact order. The code would also be “ok” if I mess up the order, e.g. $query->bind_param('is', $id, $username);.

This is a little barrier when it comes to code maintenance from my point of view. It seems like using PDO instead of MySQLi can “fix” that.