This article will look at what SQL injection means and a few ways to prevent it.
You can also check this tutorial in the following video:
1. What is SQL injection?
SQL injection is a security problem in SQL. It is one of the most common hacking techniques for any database.
An SQL injection happens when we try to receive user input from the user interface, such as a username. Instead of the Username, the user sends a SQL statement. If we execute this SQL statement into our Database without the proper checks, it can destroy the Database.
Next, we will look at all the Types of SQL injections
2. SQL Injection based on 1=1
Assume we have an HTML page with one text box for User id, and we are using a function called getUserId() to fetch the information from the UI and then select/insert/update the Database.
In the User Id, we need to input a text or a String. However, instead of a username, a malicious user attaches the string to some valid input value like 420 :
OR 1=1
The function in the web app then forms the SQL statement as:
userID = getUserId(“user_id”); + "SELECT * FROM Users WHERE UserId = "; +
Now with the input that the user has put in, the SQL statement becomes:
SELECT * FROM Users WHERE UserId = 420 OR 1=1;
In SQL, 1=1 always returns True. So, when triggered in the Database, the above statement will bear all the records from the Users table with all sensitive and non-sensitive information.
3. SQL injection based on OR “”=””
Consider the same scenario where we are to insert a Username. Instead of just a username, a malicious user may type the input as
" or ""="
Assume that in our function, the SQL statement is formed like this:
userID = getUserId(“user_id”); selectStmt = "SELECT * FROM Users WHERE UserId = " + userID;
So, the statement with the input becomes:
SELECT * FROM Users WHERE UserId = “” or ““=””
The clause OR “” = ”” will always return True. The above Query will bear all the records from the Users table, including the passwords.
4. SQL Injection attacks with Query stacking
In SQL, we can write stack queries, i.e., write multiple statements in the same line by separating them with semi-colons. Assume the same web application. A malicious user can also send Drop Tables or Drop Database or even alter Table commands and send a particular input. Doing so can cause significant disruptions and can lead to severe data loss and even Denial-of-service attacks.
For example, assume that there is a utility to retrieve the purchase history in our web application. For getting this information, we need to pass a user id. A malicious user might send the following command along with the actual username.
DROP TABLE USERS;
The Query that is formed is as follows:
SELECT * from purchase_history where user_id = ‘jane’; DROP Table USERS;
5. Retrieving Hidden Data
Malicious users can tamper with queries using the comments to retrieve information hidden with queries. Assume that in our web application, we have an Admin UI that only the administrators can use. To enter this utility, we need to pass the username and password. Ideally, the Query should be:
Select * from users where username =’admin’ and password=’fklOan20ER14p’;
However, A malicious user can pass input as
admin’--
The Query formed would be:
Select * from users where username =’admin’-- and password=’fklOan20ER14p’;
The Query would then return all the data for all admins.
6. Impact of SQL injections
Database forms the backbone of the web application and stores all the data necessary to run an application. Using SQL injection attacks, malicious users can misuse and even corrupt this data. Using SQL injections, users can perform Denial-of-Service attacks and security-breach attacks. They can also cause large-scale data loss, which can cause companies to lose millions of dollars.
7. How to Prevent SQL Injection
To prevent SQL Injection, we need to verify all the inputs before sending them to the Database. Most programming languages that we use to build the frontend have inherent validations and rules to set for username and password. We need to have all such validations to prevent malicious code and SQL statements from reaching the Database.
8. Summary
SQL injections are attacks on the database using SQL’s inherent capabilities. SQL injections are a type of vulnerability that can be devastating to the victims. However, if proper steps are taken SQL injection attacks can be easily mitigated and prevented.
Check our SQL tutorials to learn more.
Last updated on Dec. 17th, 2021
