MySQL Injection

Introduction to MySQL Injection

SQL injection is a procedure of injecting some commands of SQL by the malicious user for destroying the database or for showing other information. This can be done with the help of fields on an application or a web page. For database management from the application or web page, SQL statements are used. The interaction between a user and the database with the help of fields is very common. As the SQL statements are text keywords, so dynamic modifications of SQL statements as well as running the commands for displaying information of other users or destroying the database is also possible. Hackers can also access the sensitive information and password of other users by SQL injection.

Types of MySQL Injection Attacks

SQL injections are of three types:

1. SQL injection using 1=1
2. SQL injection using ""=""
3. SQL injection using batched SQL statements

1. SQL injection using 1=1

Refer to the below image for SQL injection using 1=1

In the first box of the diagram, there is a form field in which the malicious user is sending the value 15 or 1=1. In the second box of the diagram, there is a PHP code for accepting the value which is sent by the field of the form. That form field value is stored in a variable named $id so it can be further used for fetching the information of the user by the SQL queries. The last box of the diagram shows the SQL statement by replacing the $id variable with the form field value.

Since the condition 1=1 always gives the result as true, a malicious user can access all the information from the users table. If passwords are included in theusers table, then another user's account password is also visible to the malicious user after performing this SQL injection.

2. SQL injection using ""=""

Refer to the below image for SQL injection using ""=""

Look at the boxes given in the example. The first box of the example shows the field of the form and here the malicious user is sending " or ""=" as the values of the username and password. In the second box of the diagram, PHP code is given for accepting the variable value for the password and username and storing those values in the variable $user and $pass. In SQL queries, these values are used for a user account login.

The last box of the diagram represents the SQL query by replacing the value of the $user and $pass variables with the form field value. Here ""="" always gives the result as true, by which the hacker can access the information of all the users.

3. SQL Injection using batched statements:

Refer to the below image for SQL injection using batched statements

Look at the boxes given in the diagram. Execution of multiple statements separated by a semicolon is supported by most databases. We call these statements batched SQL statements.

In the first box, there is a form field and the malicious user is sending the value 15; DROP TABLE students; from this field. In the second box of the diagram, PHP code is given for accepting the value sent by the form field. The value is stored in the variable named $id and this value can be further used in SQL queries for fetching the information of the user.

The last box of the diagram shows the SQL statement after replacing the value of $id. Once this SQL query is executed, it will show the information of the user having a userid of 15 and it will also drop the students table.

Preventing MySQL Injection

Best Practices for Preventing MySQL Injection Attacks

Some best practices for MySQL injection prevention are given below:

• In place of dynamic SQL queries, use parameterized queries or Prepared Statements. In this, placeholders are included in the SQL query, and values are bound to these placeholders at run time. Prevention of SQL injection can be supported by the Prepared Statements as it separates the data supplied by the user from the SQL code and helps in the prevention of execution of malicious data as a code.
• Input validation should be used to ensure that the data supplied by the user is within the acceptable range and in the expected format. The data type, format, and length validation are included. For example, if there is a field to store the email address of a user, then validate that the input data is in the proper email format.
• Escape the special characters that could be used to inject SQL code. This includes converting special characters like semicolons and quotes into the respective escape sequences. It prevents the database from interpreting them as SQL code.
• Database privileges should be limited to only those users that are needed for the functioning of the application. By this, access to sensitive columns and tables can be restricted.
• Always keep your MySQL application software and database updated with the latest version of security updates.

Examples of How to Implement Preventative Measures in MySQL

Using Prepared Statements:

Here is the Java implementation of executing Prepared Statements by taking the name field input from the user. The below code takes the user input of the name field and uses the parameterized query for fetching the record from the table.

Above code prevents MySQL injection by using PreparedStatement and parameterized SQL query by assigning the parameter using the setString() method.

Using Input Validation:

Input validation can also be used for preventing MySQL injection. For example, if a field is supposed to store the email id of the user, then it is required to perform validation to ensure the correct format of the input email.

The above code raises an error in case of an invalid email format so that invalid data is not allowed in the email_id field. By validating the input, MySQL injection can be prevented.

Detecting MySQL Injection

For detecting MySQL injection, it is required to monitor your application logs so that any suspicious activity can be identified like unexpected database errors or SQL queries. Apart from that, some security tools can also be used such as SQLMap, by which a series of automated tests are performed on your application for SQL injection vulnerability identification. MySQL injection attacks can also be detected by performing vulnerability scans and penetration testing regularly.

Mitigating the Impact of MySQL Injection

If MySQL Injection has occurred, then it is required to take immediate action as follows:

• Affected server or application should be shut down so that it can not cause any further damage.
• Post-incident reviews are required to be conducted so that learned lessons can be identified and improvements can be done in the incident response process.
• Inform the relevant users like the users whose data have been affected.
• Restore the clean backup of your database.
• Identify the vulnerability that is allowing the attack occurrence and then try to patch it by modifying the code of the application, updating the input validation, or limiting the database access.

Common MySQL Injection Vulnerabilities

Some of the common MySQL injection vulnerabilities are given below:

• Injection with the help of form input fields and search boxes.
• Database error messages can also be used for injection, as the sensitive information of the database content can be revealed by it.
• It can also be done by the URL cookies and parameters.
• Injection can also be done by attackers by executing arbitrary code on the server.

See Also

• Clone Table in SQL
• SQL Joins (INNER, LEFT, RIGHT, and FULL Join)
• SQL Injection