What is SQL injection and how to prevent it in Java?

In this post, we will learn about common coding mistakes in Java, which lead to a vulnerable application. Also, we are going to learn to avoid SQLi.

What is SQL injection?

SQLi or SQL injection is an attack where a hacker manages to inject unauthorized SQL query language code into an application. Due to this, the attacker manages to change the behavior of a legitimate query before it hits the database.

These types of injection attacks happen when we execute a given computation, which dynamically generates code that will be run by another system or component. While generating the code, when we use untrusted data, without doing validation/verification/sanitization, we leave a loophole for hackers to take advantage of.

With SQL injection, hackers might be able to access/steal unauthorized information. Sometimes they can even insert, change, or delete data from the database, which can be horrible.

Example:

Let's assume we have a login page, where the user enters their username and password. If the credentials are valid, we will allow the user to proceed further.

Assume we have a class SQLInjectionTest in which we have a method validateUser(). This method fires a SQL query to verify the user identity.

Let’s assume the username is scrutiny and the password is test@123. When the user enters these credentials, the below query will be fired.

select * from users where username = ‘scrutiny’ and password =’test@123'

What will happen if the user enters the password as test@123'?

select * from users where username =’scrutiny’ and password =’test@123'’

The above query will fail. If you are using ORACLE you will get ‘ORA-01756: quoted string not properly terminated’ error.

Now imagine if the user enter’s password as ‘ or 1=1)#

select * from users where username =’scrutiny’ and password =’’ or 1=1)#

This will bypass the authentication because the above query is syntactically valid and OR 1=1 will always return true.

Suppose someone entered this as the password ‘; DROP TABLE users — 

select * from users where username =’scrutiny’ and password =’’; DROP TABLE users — 

If the above query is fired, instead of selecting a user, we would be dropping the whole table.

Prevention Techniques

To fix this we can either validate the input or use parameterized queries.

Parameterized Queries: The parameterized query is the best solution for the problem of SQL injections. We don't need to concatenate user-supplied values. Instead, we use the placeholders for those values, which will ensure that the values themselves are never part of the text of the query. The parameters are then passed to the database through a different mechanism.

FYI, a prepared statement or parameterized queries is a feature used to execute the same (or similar) SQL statements repeatedly with high efficiency. 

Let's rewrite our validateUser() to use Prepared statements.

If you enjoy our content and want to support us, please consider donating via  gpay, phonepay or paytm on +91 9920600280. Also, I’d appreciate if you’d buy me a coffee☕ 

Keep learning and growing!

-K Himaanshu Shuklaa..