Java Sql Injection Filter Example

Preventing SQL Injection Attack With Java Prepared Statement

Online data theft has recently become a very serious issue, and recent cases have been widely publicized over concerns for the confidentiality of personally identifiable information (PII). Most cases are caused by application vulnerabilities, and the most frequent threat to data security is SQL Injection. According to The Open Web Application Security Project (OWASP), SQL injection vulnerability is one of the most dangerous issues for data confidentiality and integrity in web applications, and it has been ranked as the #1 most serious web application risk. In this post, we will discuss how SQL Injection attacks work and how to prevent it using Java prepared statements.

SQL Injection

SQL injection attacks are initiated by injecting malicious SQL statements into the queries that the application makes to its database. It takes advantage of improperly sanitized data input to insert some extra characters in order to bypass authentication or gain access to sensitive data stored in the database. Without further wasting any time, let's explore SQL Injection attack in more detail. This attack example illustrates code that accepts username and password for login mechanism.

Suppose we have the following data in the MsUser table :

Normally the program would work properly as a user entered their credentials, let's say Bob and test123, and we will get a message that we have successfully logged in as Bob

Now, suppose a hacker wants to bypass the authentication mechanism, by logging in using one of the accounts that have been registered in the database. The hacker doesn't know anyone's registered username or password, so he enters the payload as follows to do SQL injection :

Because or 1=1 always return true and limit 1 only return 1 row, the hacker will login as the first user_id from the MsUser table no matter what the username is. To bypass the password authentication, the hacker use comment sequence( — ), causes the remainder query to be ignored. Now, the query is equivalent to :

          SELECT * FROM MsUser WHERE username='aaa' or 1=1 limit 1        

Now, the hacker successfully login as Bob. It should be noted that the example above is just one of the infinite number of variations that can be done to accomplish the same attack. Attacker can also use tools to automate this type of attack, such as SQLMap, jSQL Injection, etc.

Prepared Statement

To prevent SQL Injection, it is always advised to run the query in prepared statements. When we use prepared statements, we don't concatenate the user's input into the SQL statement that will ultimately been running, because it will force the developer to first define all the SQL queries, then pass in each parameter(user's input) to the query later. In other words, nothing within the user's input is treated as SQL statement. The following is an example of a login mechanism with a prepared statement:

Now, if we insert the same payload as above :

This time we a get a different message "Wrong username or password" because now the query is equivalent to

          SELECT * FROM MsUser WHERE username = "aaa' or 1=1 limit 1 --" AND password = "aaa"        

The payload will be treated as one string and stored in the username variable. So, to summarize the usage of prepared statement, we do the following to implement the prepared statement:

1. Declare a variable for the SQL statement that contains question mark as placeholders
2. Create prepared statement instance using conn.prepareStatement(query)
3. We call the setter methods to set the placeholders to the values we want to pass into the statement
4. We run the statement using query.executeQuery()
5. We process the result the same way as we do when using an old regular statement
6. Close the prepared statement and resultset as they may cause memory leakage

That's all guys, thank you for reading and for helping me grow as a writer. If you have any questions or feedback, please drop a comment :)

coveybarigoinathe39.blogspot.com

Source: https://medium.com/swlh/preventing-sql-injection-attack-with-java-prepared-statement-259611281e4d

Share this post