One of the most common web hacking techniques, an SQL injection is a method for attacking and manipulating underlying databases. From providing access to protected data, to gaining access to manipulate the database of the targeted application, this tool is a common threat for most website and application managers. Our guide walks you through this widespread threat so you can protect your own applications from it.
What is a SQL Injection?
Any experienced creator or manager of a data-driven application knows the threat of an SQL injection. But what is an SQL injection? Put simply, it’s an easy-to-implement and effective method for hackers to subvert application and database security for purposes including mining sensitive information and editing or manipulating the underlying database. Worried about what this might mean for your platform? This guide provides a basic review of how an SQL injection works, so you can plan ahead before a hacking incident happens.
SQL Injection (SQLi) is a way to attack applications and manipulate the underlying database by injecting malicious SQL statements.
A successful SQL injection allows attackers to gain unauthorized access to sensitive data, as well as modify and delete the data of the application.
SQL Injection is one of the most popular attacks because it is relatively easy to implement.
Consider a website that displays courses. To display a specific course, the course name is passed as the GET parameter in the URL:
This application then constructs the following SQL query to get the data of the requested course from the database:
query = “SELECT * FROM Courses WHERE name='" + courseName + "';"
In case the value of the input is not correctly filtered by the application, the statement can be manipulated by providing the following course name as the parameter:
https://www.sololearn.com/Course?name=HTML' OR '1'='1
This will result in the following SQL query:
SELECT * FROM Courses WHERE name=’HTML’ OR ‘1’=’1’;
which will cause the application to return the data of all courses present in the database (even unpublished), instead of the one requested.
This is a simple example to demonstrate how the query can be manipulated. Imagine doing the same to a query which selects user-specific details, or is used to authenticate a user based on the input username/password. SQL Injection can be used to query all the users of the application, or even destroy the whole table!
Let’s look at an example of table deletion, when the application allows execution of multiple queries:
In the same URL from above, consider having the following as the input parameter:
HTML’;DROP TABLE Courses;SELECT * FROM Courses WHERE ‘1’=’1
This will result in the following SQL queries being executed:
SELECT * FROM Courses WHERE name=’HTML’;DROP TABLE Courses;SELECT * FROM Courses WHERE ‘1’=’1’;
This will drop the Users table from the database.
Another SQL Injection technique is to add SQL comments -- at the end of the parameter value to block the rest of the query:
SELECT * FROM Courses WHERE Name=’HTML’ OR ‘1’=’1’ -- AND Published=1;
SQL Injection can allow the attacker to query database information, such as table names or database system versions, as well as query data from other tables of the database, by using the UNION statement.
Want to learn more about SQL statements? Check out our free SQL Tutorial now!
Let’s look at another example:
Consider a web page that is used for login using username and password input fields.
The entered username and password are used to query the corresponding user from the database and, if a user matches, the login succeeds:
query = “SELECT * FROM users WHERE username=’”+username+”’ AND password=’”+password+”’;”
Now, if the input fields are not correctly filtered, the attacker can use administrator’-- as the username and a blank password, resulting in the following query:
SELECT * FROM users WHERE username=’administrator’ -- AND password=’’;
which will return the administrator user’s data and successfully login the attacker.
There are a number of ways to prevent SQL Injection attacks.
Parameterized queries can be used instead of embedding input values in SQL queries.
In many cases, the SQL statement is fixed, and each parameter is a scalar.
This way, each parameter has a known type and any unknown input would be considered as invalid.
One way of achieving parameterized queries is using ORM (Object-Relational Mapping) libraries that automatically generate queries from code.
Escaping special SQL characters is another way of preventing SQL injections (for example, by replacing single quotes with double quotes).
In PHP it is usual to escape parameters using the mysqli_real_escape_string() function before sending the query to MySQL.
There are other similar functions available in other programming languages and for other database systems.
Setting the correct database permissions is also a common way of reducing the risk of SQL injection attacks. This is achieved by limiting the permissions of the database login used by the application to only what is needed (for example, letting it only run SELECT queries on the specific database table).
Now that you know the basics, are you worried that your SQL skills might not be adequate to stop an attack? Don’t worry! The SoloLearn SQL Fundamentals course can help you fill any gaps in knowledge, with targeted tutorials on SQL essential elements and quizzes and challenges to test your skills before working on a live application. Give it a try!