Question 1: What is SQL injection attack?

Question 2: How many simply ways to prevent sql injection attack?

Question 3: Why should single quotes must be added when dealing params with mysql_real_escape_string?

What's SQL Injection attacks

The damage of SQL Injection attacks

Samples of SQL Injection attacks

How to prevent SQL Injection attacks

SQL injection attacks refer to constructing special input as params, passing them into the background processing progaram (such as PHP code), and execute SQL statements to preform the operations desired by the attacker, thereby achieving the purpose of the attack.

SQL injection attacks are very harmful, and are one of the most common attachs on the Internet. Generally, vulnerabilities can easily be left if you are not careful when writing code, but they are very easy to prevent.

The attacker may be able to bypass the system's security measures, such as bypass the login account and password, and enter the system directly.

The attacker may be able to read and modify the data in the database at his will.

The attacker may be able to gain access to the system by operating on the database, directly enter the system, then perform any operation.

This is a simple login UI.

The corresponding HTML code is as following:

The PHP code that handles the login process is as following:

When we input "admin' or 1 = 1#" at the username field, and 123456 at the password field, then it results as following:

Accroding to this, the generated is as following:

The execution result of this $sql will lead to the execution of operations after successful login. In this way, we successfully logged into the system without a username and password.

In the same way, we can execute any other database operations as wish, to get all data from the database step by step, or even we can get login permissions of the server.

1. Principle of minimize permissions

2. Close error display

3. Escape query parameters

4. Use statement to execute query

Database user for virtual host should not be superuser, such as root. You should setup a seperated user for each virtual host. The user should be allowed to connect to the database server from the web server's IP only, and should has permissions to the database used by the web only, not all IPs and all databases. And it should has permissions as few as possible needed, not full permissions. So if any problem happened, we can minimize the damage to current database only, not influence other databases and websites.

For PHP, you should setup php.ini as following:

display_errors = Off

 

If you do not have permission to modify php.ini, you can setup it within your code:

ini_set(“display_errors”, “Off”);

 

So if there's any problem occupied from your code, it will just display blank page only, not the whole detailed error message. By this, we can reduce disclosure of code and server configurations.

PHP provided a funtion named mysql_real_escape_string, to escape special characters used by SQL query. The function can be used safely with mysql_query().

For the previous login handle code, we can add mysql_real_escape_string to $sql as following:

We still input "admin' or 1 = 1#" at username field and 123456 at password field. It results as following:

This time, we prevent the injection successfully.

 

Important when using mysql_real_escape_string:

All parameters passed to query MUST use this function to escape, including $_GET, $_POST, $_SESSION and $_COOKIE

Parameters MUST be surround with single quote. The following is correct:

The following is error, it has no effect.

It's strongly recommended to use mysqli instread of the tranditionally mysql methods.

For the previous login handling, we can use mysqli to change it to statement query. It can avoid SQL injection effectly.