SQL Injection in MySQL

Back to: MySQL Tutorials for Beginners and Professionals

SQL Injection in MySQL Database

In this article, I am going to discuss SQL Injection in MySQL Database with Examples.

What is SQL Injection?

SQL injection is a method where a malicious user can inject some SQL commands to display other information or destroy the database, using form fields on a web page or application. The SQL statements are used to manage the database from a web page or application. It is common to let the users interact with the database using form fields. Since the SQL statements are text keywords, it is possible to dynamically change SQL statements, and run some SQL commands which may display other user information or destroy the database. By using SQL injection, a hacker may get access to other users’ passwords and other sensitive information.

Types of SQL Injection

There are mainly three types of SQL injections:

  1. SQL injections using 1=1
  2. SQL injections using “”=””
  3. SQL injection using batched SQL statements
SQL injections using 1=1

SQL injections using 1=1 in MySQL Database

Take a look at the 3 boxes, the first box shows, form field where the malicious user is sending the value “15 or 1=1”. In the second box, PHP codes accept post variable value sent via a form field. The post variable value is stored in the $id variable, which is used in SQL queries to get user information. The third box shows the final SQL statement after replacing the $id value in PHP codes. Since the “1=1” condition is always TRUE, the malicious user can access all the user’s information. If the table contains a password data column, the password of other user accounts will be visible by using this SQL injection.

SQL injections using “”=””

SQL injections using ""="" in MySQL Database

Check out the example’s boxes. The first box shows, form field where the malicious user is sending the username and password values as ” or “”=”. In the second box, the PHP code accepts the post variables value for username and password, and the values in $user and $pass variables. These variables are used in SQL queries to log in to the user’s account. The third box shows the final SQL statement after replacing the $user and $pass values in PHP codes. Since WHERE “”=”” is always true. The hacker will be able to get the information of all the users.

SQL Injections using batched statements

SQL Injections using batched statements in MySQL Database

Take a look at the 3 given boxes, most of the databases supports executing multiple statements separated by semicolon. These statements are called batched SQL statements. The first box shows the form where the malicious user is sending the value 15; DROP TABLE students; In the second box, PHP codes accept post variable value sent via a form field. The post variable value is stored in the $id variable, which is used in SQL queries to get user information. The third box shows the final SQL statement after replacing the $id value in PHP codes. The SQL statement will display information of the userid 15 and then delete the table name ‘students’;

SQL Injection Example using MySQL Database

Let’s take an example of a simple web application with a login form. The code for the login form will be as follows:

<html>
<head>
<title>Login Form</title>
<body>
<form action=‘index.php’ method="post">
<input type="email id" name="email" required="required"/>
<input type="password" name="password"/>
<input type="checkbox" name="remember_me" value="Remember me"/>
<input type="submit" value="Submit"/>
</form>
</body>
</html>

This form accepts the email address and password and then submits them to a PHP file named index.php. It also has an option of storing the login session in a cookie. This form uses the POST method to submit the data which means the value is not displayed in the URL.

Now suppose the statement for checking user id is: SELECT * FROM users WHERE email = $_POST[’email’] AND password = md5($_POST[‘password’]);

Now we will illustrate SQL injection attacks. Use the below code to create a database and table:
create database userdb;
use userdb;
CREATE TABLE users (
  Id INT NOT NULL AUTO_INCREMENT,
  Email VARCHAR(45) NULL,
  Password VARCHAR(45) NULL,
  PRIMARY KEY (`id`)
);

insert into users (Email, Password) values ('info@dotnettutorials.net', md5('abc'));

Now use the below code to run the SQL:

SELECT * FROM users;

You will get the following output:

SQL Injection Example using MySQL Database

Now suppose the attacker provides the following input in the email address field:

xxx@xxx.xxx’ OR 1 = 1 LIMIT 1 — ‘ ]

xxx for the password. The SQL statement will be:

SELECT * FROM users WHERE email = ‘xxx@xxx.xxx’ OR 1 = 1 LIMIT 1 — ‘ ] AND password = md5(‘1234’);

The output will be:

SQL Injection in MySQL Database with Examples

Here, xxx@xxx.xxx ends with a single quote which completes the string quote, OR 1=1 LIMIT 1 is a condition that will always be true and limits the returned results to only one record & ‘AND is the SQL statement that eliminates the password part.

How to secure the database?

Protecting the database from SQL injection is extremely easy. Always filter the input received via form fields. Use the mysqli_real_escape string function to escape the harmful characters. Use PHP Data Objects (PDO) to safely execute the SQL statements. These are the methods used in PHP applications. If you are using a different programming language, make sure you read database communication and functions to escape SQL Injections.

Here, in this article, I try to explain SQL Injection in MySQL Database with Examples and I hope you enjoy this SQL Injection in MySQL article.

Leave a Reply

Your email address will not be published. Required fields are marked *