Today, most websites and applications are powered by databases. Most of what you see on a web page or mobile screen is fed from a database. Whenever you request a web page, it fires certain SQL queries to its database to fetch required data. If these queries are not filtered by your website then they can easily be misused by attackers to collect data from your database, even modify or delete data. Therefore, it is important to put proper checks and measures in place to prevent such SQL based manipulation. In this article, we will learn what is SQL injection and how to prevent it.
What is SQL Injection
SQL Injection is an attack where the attacker injects malicious SQL statements into your website via its input form fields such as textboxes and textarea. They can also be inserted via GET parameters in requested URLs. Once these queries are executed or stored into your database, they may help attackers gain unauthorized access, retrieve data, modify or even delete data from your database. They exploit input processing vulnerabilities present in your website or app. They are applicable for all kinds of databases including popular ones like MySQL, PostgreSQL, SQL Server and Oracle.
While most web development frameworks already have some checks to prevent SQL injection, they are not comprehensive. Web developers must work proactively to plug in any SQL injection vulnerabilities on their site. This is a high priority issue that can be easily used for data theft, unauthorized access and even manipulation of data and user permissions.
How SQL Injection Works
Most websites and apps contain user forms with input fields for sign up, login, etc. They generally contain one or more text input fields. An attacker will simply enter a JavaScript code with an SQL query in it, while entering username, email address or any other input. Sometimes they may even use a tricky SQL query. This is called as a malicious payload and it gets saved in your database.
When a user uses your website, then this query may get executed and cause significant damage to your database as well as website. SQL Injection can be used to cause serious harm to your database such as stealing login credentials of other users and impersonating them, altering financial data stored in your database, even deleting tables and rows altogether.
Let us look at a simple example to understand this problem. Let us say you have the following PHP code for logging in users after receiving username and password from login form.
$uname = $_POST['username'];
$pword = $_POST['password'];
$sql = "SELECT * FROM users WHERE username='$uname' AND password='$pword'";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0)
{
// User logs in
}
else
{
// Invalid credentials
}
In the above code, we directly insert user input username and password in SELECT statement. Let us say the attacker enter admin’– as username, then the SQL query used in our code becomes the following.
SELECT * FROM users WHERE username='admin'--' and password=''
In MySQL, double dash(–) stands for comments and whatever follows after it is treated as a comment by MySQL. So the above query effectively becomes the following, which allows attacker to skip password check and log into MySQL database as administrator.
SELECT * FROM users WHERE username='admin'
This is just a simple example. Attackers use sophisticated SQL queries for different purposes such as retrieving data, manipulating data or even deleting it.
Types of SQL Injection Attack
There are several ways to craft SQL Injection attack. Over the years, attackers resort to 3 main types of SQL injection.
1. Error-based SQL Injection
This is the preliminary type of SQL Injection used by attackers to basically get an idea of your database. In this case, the attacker will send an SQL query that invokes an error and returns an error message. Such queries have deliberate syntax errors such as misplaced quotes or operators that will definitely trigger an error if executed in your database. Here is an example of requested URL that contains a malicious GET parameter.
https://www.example.com/index.php?name=1'
In the above request, we use single quote to trigger an error in website’s database. In this case, you will see the following error message.
You have an error in your SQL syntax; check the manual corresponding to your MySQL server version for the right syntax to use near ‘‘VALUE’’.
The above message easily tells the attacker that your site’s database is MySQL, and that your website is running SQL queries without proper checks. Once the attacker has determined your database type, then they can create more complex queries to exploit its vulnerabilities.
Please note, this error occurs only if the query is directly fired to your database, without any sanitation.
2. Union Based SQL Injection
In this approach, the attackers use UNION keyword to append their malicious SQL query to the one execute by your website or app. Let us say your website runs an SQL query as shown, where user_input is retrieved from input textbox on your site’s web page.
SELECT name, email, address FROM users WHERE name = '[user_input]'
Let us say the attacker uses the following value as user input.
' UNION SELECT password, NULL, NULL FROM users --
When the above payload is substituted for user_input, you get the following SQL query.
SELECT name, email, address FROM users WHERE name = '' UNION SELECT password, NULL, NULL FROM users --'
This will return not only the name, email, address but also passwords.
Such SQL queries can also be passed via GET parameters in requested URL. Let us say your web page has following URL.
https://www.example.com/products.php?brand=1
Instead, let us say the attacker requests the following URL.
https://www.example.com/products.php?brand=-1 UNION SELECT 1, 2, 3
This will allow attackers to obtain values from other tables also. Here is an example to retrieve username and passwords.
https://www.example.com/products.php?brand=-1 UNION SELECT username, password from users
3. Blind SQL Injection
In this type of attack, the attacker sends SQL queries to website or application and uses the response to get more information. Let us say you have the following request where you provide item ID.
https://www.example.com/products.php?id=999
Let us say the above URL results in the following SQL query being fired to your database.
SELECT product_name, product_description FROM products WHERE ID = 999
The attacker will simply append or 1=1 to the above URL.
https://www.example.com/products.php?id=999 or 1=1
This results in the following SQL query.
SELECT product_name, product_description FROM products WHERE ID = 999 or 1=1
Since 1 is always equal to 1, the query will return a list of all product names and descriptions, even the ones you do not have access to.
This kind of attack is mainly used to confirm if such an attack is possible. Once it is confirmed, the attacker will start sending more complex payloads to extract more information.
You may be surprised to know that attackers often employ multiple kind of the above attacks to crack the website’s defense and they are mostly automated using bots these days.
How to Prevent SQL Injection Attack
There are several ways to prevent SQL injection attack on your website. They deal with input sanitation and restricting database access. We will learn about the important ones below.
1. Input Validation
Since almost every SQL injection attack involves modifying or manipulating user input to run database queries, it is essential to validate and sanitize user inputs before you process them on your server.
First of all, never user user input values directly. Always run them through a set of input validations that check for format and accepted values. Also, escape special characters such as quotes, brackets, operators, etc. that are obviously unexpected in input fields such as name, email, etc.
Also, make sure requested URLs conform to expected format. Attackers exploit GET parameters to send malicious payloads to websites. For example, see if your URL requires any GET parameters. If not, then simply don’t process requests with URLs containing GET parameters. Even if some URLs need GET input, make sure that the number and type of GET parameters in requested URL matches what is required to process the request. Nothing extra should be allowed.
Also, disable display of database errors on your website since it may not benefit end users but only help malicious attackers.
Every web framework provides out of the box capabilities to escape SQL injection. Sometimes it may be turned off by default. You may need to enable it. Such features are very useful in sanitizing user input.
2. Use Parameterized Statements
Parameterized statements are those SQL queries which contain placeholder variables for user input. In this case, the database automatically recognizes user input and securely sanitizes them before execution.
Here is a simple example of parameterized query using Python.
cursor.execute("SELECT * FROM users WHERE username = ? AND password = ?", (username_input, password_input))
Here the database will recognize that the first ? stands for username_input variable’s value and the second one stands for password_input variable’s value.
This is much better than simply concatenating these variables into your query as shown.
cursor.execute("SELECT * FROM users WHERE username = "+username_input+" AND password = "+ password_input)
In this case, the database will receive concatenated string with variable values substituted beforehand. So it cannot identify and sanitize user input values.
3. Use Stored Procedures
Stored procedures are sequence of SQL statements that are saved for reuse. They provide certain amount of isolation from application code since stored procedures are kept separate. When you also use parameterized queries in stored procedures they become extremely secure.
In this case, database treats all user inputs as data instead of SQL query to be executed. So it validates and sanitizes them before using them in stored procedure.
4. Restrict Database Access
One of the easiest ways to prevent SQL Injection attacks is to control who can access your database. To begin with, use Network firewalls to control access to your database. Typically, websites deploy web servers and databases on different machines. If you use such an architecture, then ensure your database firewall allows incoming connections only from web server and not anywhere else.
You can also use applications like ModSecurity that will continuously monitor the connections to your database and automatically block malicious ones.
As seen above, attackers rely on database error messages to learn more about your database. Limit error messages using RemoteOnly mode which will display complete error messages only to local users and display a terse message for remote connections.
Encrypt important data such as passwords, credit card details, financial information, etc. so that even if attackers get their hands on this information, it cannot be used unless they know your secret encryption keys, which is nearly impossible.
Often it is seen that employees indulge in password sharing. Some organizations even use shared database users and accounts. Avoid them at all costs. Let’s face it. People are generally careless while sharing passwords and your organization should not pay the price for it.
5. Use Least Privilege Principle
Another powerful way to prevent SQL Injection is to provide only required permissions to database users. For example, here is an SQL query to allow database user to run only SELECT statements.
GRANT SELECT ON database_name.table_name TO 'username'@'ip_address' identified by password;
In the above query, we allow only SELECT privilege, to user with username and password. It means the user can run only SELECT query on the said database table. Here too, we allow only queries to specific table, from specific IP address. This ensures that even if attackers gain access to this user via SQL injection, they are not able to cause much damage.
6. Use Application Firewalls
Sometimes the SQL injection vulnerability may not be in your code but a third-party library or module that you are using. Since you may not be able to modify its code, you may want to use an application firewall to clean up user input fed to your application. Application firewall is different from network firewall. It controls the traffic to and from a specific application or service only.
7. Monitor Applications & Databases
If you can afford it, then use regular audits and code scans to look for vulnerabilities. There are many firms that will help you with manual checks. There are also several automated tools for this purpose.
Security audits and penetration testing are often employed in financial organizations such as banks for regulatory compliance. If your website used a lot of financial information, you may want to look into this option.
8. Update Applications & Databases
Websites and applications use several libraries, modules and third-party tools to work properly. They all release regular updates which contain fixes, patches and security updates. It is important to regularly update all applications, database and plugins used by your website to avoid all vulnerabilities including SQL Injection Attack.
We never know, one of the plugins used on our site may have SQL injection vulnerability. Since it is impossible to review codes of all plugins and libraries used on our site, it may give attacker an entry into our database. So make sure you routinely update your applications and databases.
9. Test for SQL Injection Vulnerabilities
Another simple way is to just test for SQL injection vulnerabilities by requesting URLs with malicious payloads yourself. See how your website responds and make changes accordingly. Often many software developers leave out this part and rely completely on their code to the heavy lifting.
It is very easy to find SQL injection payloads on the net. Search for a few articles that illustrate how to craft an SQL injection payload. Try executing it on your website and modify your code accordingly.
Conclusion
In this article, we have learnt what is SQL Injection attack, types of these attacks and how to prevent SQL Injection in our site. Input validation and restricting database access are the two most powerful ways to block SQL injection attacks. There is no one size fits all solution to this problem. You will need to implement several or all of the above solutions to protect your database.
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
Also read:
How to MySQLDump Only Specific Tables
Difference Between MyISAM and InnoDB
Single Quotes, Double Quotes and Backticks in MySQL
Difference Between Inner Join & Outer Join
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.