Introduction: What is SQL injection
Article for web developers and site administrators.
An SQL injection is as its name suggests an injection or insertion of SQL code via data transmitted from a website. A successful and properly exploited injection can recover sensitive information from a database or modify / delete / add data. In general, all actions related to a database are possible. Usually this type of injection concerns PHP with a SQL database but other languages like ASP can also be involved.
This type of injection usually occurs when user data is used without being filtered or verified.
What exactly can be done with SQL injection?
Usually the hacker will seek to retrieve sensitive information from your database.
Even if you know your visitors or do not think you know a user capable of attacking your site, you should know that a hacker will simply search for vulnerable sites using what is called a Dork Google . A Dork is a very precise keyword containing a model which makes it possible to recover potentially fallible sites.
There are no legitimate reasons to display all possible dorks here and besides they are very numerous, just know that you can search for a specific page that could correspond to a fallible page of your site.
By connecting to such a page, the SQL query that we place on our site usually looks like:
"SELECT id FROM users WHERE name = 'Admin' AND password = '". $ _ POST ["password"]. "'"
We therefore select the identifier of the user whose name is Admin and whose password corresponding to the one sent by the user who wants to connect.
This is NEVER to do, because if you post:
'OR' 1 '=' 1
as a password we get the following query:
"SELECT id FROM users WHERE name = 'Admin' AND password = '' OR '1' = '1'"
What gives, translated into French: "
Select the identifier of the user whose name is admin and whose password is empty OR 1 is equal to 1"
Thus the password will not be empty but 1 is equal to 1 so access is allowed , the identifier is selected .
This is the very classic example that is often talked about during databases.
How do I know if my site is fallible?
We can search for problems from our source code directly but also, and more simply, by adding a
'
at the end of a fallible url. If an error appears on your site type, it is that there is potentially a problem:
Erreur dans l'exécution de la requête 'SELECT * FROM galerie WHERE id = 2''. Message de MySQL : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1
The error appears on the site because " id=2' "
is directly used to form the query while " ' "
is a special character of SQL. This causes a syntax error.
This error therefore tells us that the data entered by the users are not checked on the server side, and that there is a good chance that we can go further.
I'm not going to continue until the end but know that then the hacker can recover the table names and display their contents. The password (the admin table for example) appear generally encrypted on the site. This is why it is essential to encrypt user passwords in databases.
The other way to know if his site is fallible is to scan his source code.
There are two ways to scan a source code: the manual way and the automatic way .
The manual way is, as the name suggests, to look for our pieces of code that communicate with the database and ensure that they are robust (see below in the article).
The automatic way is to analyze its code with a tool. OWASP offers a list of tools like this:
Blind SQL injection
As its name suggests, the "blind" SQL injection consists in exploiting a fallible site in the same way as the one we saw, except that the result (the error messages) are not not displayed on the page.
Again, complete tools are used to automate all of this. Especially if a company uses a team of ethical hackers to test the security of their systems without having to provide the source code.
How to protect yourself
Let's come to the essential point, guard against SQL injection attacks.
If we take the example of the beginning:
"SELECT id FROM users WHERE name = 'Admin' AND password = '". $ _ POST ["password"]. "'"
We use here what the user sends directly in the request.
"SELECT id FROM users WHERE name = 'Admin' AND password = '" .mysqli_real_escape_string ($ _ POST ["password"]). "'"
The addslashes () and magic_quotes_gpc () functions are also used but do not protect as well as mysqli_real_escape_string () .
A way that tends to generalize but has a slight impact on performance is the use of prepared commands
The stored procedures require more knowledge but can also be used. The identification will remain well protected within the procedure and can no longer be diverted.
Finally, it is preferable to use limited access user accounts to prevent the modification or deletion of database elements. And possibly check the data with regular expressions or use tables containing all the possible results.