Who is this post for?

 

Here we talk about PHP, MySql, and Cyber Security.

 

If you already know all about this topic it’s time to skill up by reading this:

 

Domain-driven design quickest {the basics}

 

If this post is too complicated for you at the moment you can opt for this one instead:

Good Practices: PHP Security, How to manage password

 

The problem

 

There are 2 actors in this story.

 

One is you, a friendly and motivated web developer who wants to build secure web applications.

 

The other is Lucy

 

Lucy is not as friendly,

 

She likes tech too but what she really wants is to get the data from your applications’ users.

 

If you ever worked on any web development project you know that one of the most important parts of it is to manage data.

 

Passwords are like underwear. Don’t let people see it, change it very often, and you shouldn’t share it with strangers.

Chris Pirillo

 

Some data you will work on is non-persisted but sometime it would need to be used time and time again,

 

In order to do that, this data needs to be stored somewhere.

 

The majority of the time that somewhere is a database.

 

A database is a systematized group of information generally held in a computer.

It is maintained by a database management system.

 

The data and the DBMS and the application that is associated with them, are referred to as a database system.

 

Like when you talk to a friend of yours or familiar, in order to effectively ‘talk’ to a database you need a standard or a language that both of you understand.

 

In, this case the language is called SQL

 

SQL stands for Structured Query Language and it is used to do operations on a database.

 

It is currently the standard language for DBMS.

 

SQL is formed by a series of statements, starting with selected keywords that perform actions such as create, read, update and delete items.

 

Some of the most popular versions of it are Oracle, Microsoft SQL Server, and MySQL.

 

Depending on the type of data contained inside the database, communicating with it can be a very dangerous task if not done properly.

 

There are many risks, just look up how many attacks and leaks there have been during the last couple of years.

 

And specifically talking about attacks, one of the most common attacks is the SQL injection attack.

 

What does SQL injection mean?

 

The shape of the SQL language is pretty standard…

 

…and Lucy knows it too.

 

Depending on what you need to do there will be the same keywords and structure for many of the statements.

 

Look at this example below.

 

 

/api/users?role=admin

SELECT *
FROM users
WHERE role = 'admin'
AND permission = 'true';

 

Even if this is the first time you see some SQL code what we are trying to achieve here is pretty straightforward.

 

By hitting the /api/users endpoint, we are saying to your application to find (select) all the users (*) that have (where) the role equals to ‘admin’.

 

A SQL injection attack is when an attacker injects a part of a SQL query via the input from the client to the application.

 

If Lucy knows what she is doing she can easily read, insert or delete very sensible data from our database or even do some operations to the SO of the server.

 

This is something you do not want!

 

How can she do it?

 

Have a look at this

 

api/users?role=admin'--

 

Now the query will look more like this;

 

SELECT *
FROM users
WHERE role = 'admin'--'
AND permission = 'true';

 

What the double dash does is exclude everything that appears after it

 

In this way, Lucy effectively deleted the permission part of our query.

 

Ok, but my user's table has no admin string in the role field so she cannot get any information. I am safe!

 

Are you?

 

Let’s rewrite the endpoint call, shall we?

 

api/users?role=admin'+OR+1-1--

 

This becomes

 

SELECT *
FROM users
WHERE role = 'admin'
OR 1-1--'
AND permission = 'true';

 

Now even though you do not have any admin field in your user's table the query still returns all the users because 1 is always equal to 1 and by adding the double-dush the database never checks for permissions.

 

 

 

 

Hot to prevent SQL injection attacks?

 

There is only one way to avoid SQL injection attacks and you must keep it in mind every time you work with a database.

 

You MUST separate the data from the language.

 

The data is like the subject of the sentence and it must stay away so it is never interpreted by the SQL parser.

 

You must ALWAYS use prepared statements and parameterized queries.

 

Those statements are sent to the database server separately from the data.

this makes it impossible for Lucy to add their own code to the SQL query

 

Let’s see an example in code to explain what you have just read.

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");$role = "admin";

$statement = $mysqli->prepare("SELECT email FROM Users WHERE role=?");$statement->bind_param("s", $role);

$statement->execute();

$statement->bind_result($email);

while ($statement->fetch()) {
    printf("the %s email is: %s\n", $role, $email);
    // The admin email is: admin@mail.com
}$stmt->close();

 

Note that when using mysqli or PDO in order to access a MySQL database, prepared statements are not used by default.

 

You need to disable the emulation.

 

Do this with the following command.

// PDO
$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);// mysqli
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // error reporting
$dbConnection->set_charset('utf8mb4'); // charset

 

Prepared statement SQL injection

 

So,

 

You now know there is a risk in dealing with databases and you have seen how to edit your code.

 

What does it all means though?

 

Simply put, the SQL statement passed to the prepare function will be parsed by the database server.

 

People ask me all the time, ‘What keeps you up at night?’ And I say, ‘Spicy Mexican food, weapons of mass destruction, and cyber attacks.’ — Dutch Ruppersberger

 

Given we have specified what parameter we want to add, in our case role=?, the database engine knows where to apply its filter (we could have also used the syntax role=:name instead).

 

What happens next is that we call the execute() method from that mysqli_stmt object the statement is combined with the parameter, in our case the role.

 

The value of the parameter is combined with the compiled statement and not the SQL string.

 

This means that in a way we are sending the actual SQL separately from the parameters, limiting the risk of executing something malicious.

 

Given we gave the ‘s’ as parameters, any parameters sent when using the prepared statement will just be treated as strings.

 

We are safer than before and your enemy Lucy now cannot inject anything anymore.

 

Here is a nice pic of her now!

 

 

Also, using prepared statements when executing the same statement multiple times will only be parsed and compiled once.

 

That is a big speed advantage.