What is SQL injection?
An SQL injection is a type of code injection technique where an attacker uses a piece of SQL (structured query language) code to manipulate a database and gain access to potentially valuable information such as user details and passwords.
SQL injection usually occurs when we ask a user for input, like their username/userid, and instead of a name/id, the user gives an SQL statement that will unknowingly run on the database.
Hackers type SQL commands into any input or select fields and if code is not handled correctly at the backend hackers can gain access to a system and reveal the data held inside.
Let’s see one following example where the function accepts inputs that will come from the API request from the user. We will see the possibilities of SQL injection attacks on the written code.
In the example below we are getting the list of students with student names and marks for the student whose name we are getting from the input.
Once we pass the name to the function we will get the list of all students matching that name from the table.
If instead of the student’s name if the user/hacker adds the sql statement to it like below, then the user will get access to the database and can read the data from any table.
In the above update input, the hacker has added the extended query to fetch the user name and password from the user’s table, once our function is called with the above input name, the code output will be as shown below.
Hackers can also get the data for all students instead of any particular student by passing the below-updated input.
Output for the above sql injection query is as below.
You can copy the above code from SQLInjectionExample and DatabaseConnector.
How to prevent SQL Injection?
Now that we are aware of what SQL injection is, now we will check how we can prevent the sql injection.
We can prevent SQL injection by below techniques
- Using Parameterized queries
- Using JPA Criteria queries (We can check this later in JPA)
- Validating user input data
Using Parameterized queries
Instead of using the Create Statement, we can use the Prepared statement which accepts the parameters, so whenever the hacker tries to send the modified sql input, that whole string will be considered as a field value instead of the conjoined query.
The above example can be updated as below using the Prepared statement. You can copy code for the following class from ParameterizedQueryExample
The output for the above example is as shown below.
For the first input string even if the hacker is trying to use sql injection while setting the value in the parametrized query that value will be set as the whole string and since we don’t have the database entry for the name “Linda’ or ‘1’ = ‘1” we will get no results from the database.
The above query is executed like the below on the database side.
Validating user input data:
These are backend code-level checks before sending the value inserted by the user/hacker to the actual function. We can have filters or any regex checks for the values.
For example, if we are expecting the numeric value from the user, then we should validate that the value entered by the user has only numbers and no characters or any special character is present. We can add the checks based on the requirement.
It is always a good habit to use parameterized queries and validate the data before passing it to the database.
We can also check the SQL Injection Prevention Cheat Sheet, here a list of techniques is available, including database-specific ones.
