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:

SQL injections using 1=1
SQL injections using “”=””
SQL injection using batched SQL statements

SQL injections using 1=1

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 “”=””

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

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:

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:

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.

Dot Net Tutorials

About the Author: Pranaya Rout

Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.

Registration Open For New Online Training

Enhance Your Professional Journey with Our Upcoming Live Session. For complete information on Registration, Course Details, Syllabus, and to get the Zoom Credentials to attend the free live Demo Sessions, please click on the below links.