What is a SQL Injection?
In simple language SQL injection is injecting malicious sql code into the application's sql that may help attacker to retrieve sensitive information like user name / passwords etc OR it can also be used by hackers for login without authentication.
For example, We have a human resource management system, where in employee logs in and can view his / her sensitive information like attendance, salary etc. To log on to system every employee requires username and password. Now suppose below function is used to do authentication.
private boolean isAuthenticate(String userName, String password) {
Statement stat = connection.createStatement();
String query = "SELECT 1 FROM EMPLOYEE WHERE USER_NAME = '"+userName+"' AND PASSWORD = '"+password+"';";
ResultSet rset = stat.executeQuery(query);
if (rset.next()) {
return true;
}
else {
return false;
}
}
if the above query fetches result then allow the user to enter into the system else don't allow. You might think that the above process is correct but it's not like that, the above function has a serious flaw. let's see how.
If I enter correct userName and password the query would look as shown below, which is correct.
user name = scott
password = tiger
Query - SELECT 1 FROM EMPLOYEE WHERE USER_NAME = 'scott' AND PASSWORD = 'tiger';
Suppose I enter below values
user name = scott
password = ' OR '1' = '1
Now the query would be
Query - SELECT 1 FROM EMPLOYEE WHERE USER_NAME = 'scott' AND PASSWORD = '' OR '1' = '1';
Look at the OR condition. Now the above query will fetch result and user will be allowed to enter the system without password.
To solve the above issue we can use Parameterized Queries in JDBC it's called as PreparedStatement. Parameterized Queries is a precompiled SQL statement. The SQL query uses a parameter instead of injecting the values directly into the query. Lets modify the above method with PreparedStatement.
private boolean isAuthenticate(String userName, String password) {
String query = "SELECT 1 FROM EMPLOYEE WHERE USER_NAME = ? AND PASSWORD = ?";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setString(1,userName);
pstmt.setString(2,password);
ResultSet rset = pstmt.executeQuery();
if (rset.next()) {
return true;
}
else {
return false;
}
}
Prepared statements are the ability to set up a statement once, and then execute it many times with different parameters. They are designed to replace building ad hoc query strings, and do so in a more secure and efficient manner. To look at complete JDBC Prepared statements example, click here.
In simple language SQL injection is injecting malicious sql code into the application's sql that may help attacker to retrieve sensitive information like user name / passwords etc OR it can also be used by hackers for login without authentication.
For example, We have a human resource management system, where in employee logs in and can view his / her sensitive information like attendance, salary etc. To log on to system every employee requires username and password. Now suppose below function is used to do authentication.
private boolean isAuthenticate(String userName, String password) {
Statement stat = connection.createStatement();
String query = "SELECT 1 FROM EMPLOYEE WHERE USER_NAME = '"+userName+"' AND PASSWORD = '"+password+"';";
ResultSet rset = stat.executeQuery(query);
if (rset.next()) {
return true;
}
else {
return false;
}
}
if the above query fetches result then allow the user to enter into the system else don't allow. You might think that the above process is correct but it's not like that, the above function has a serious flaw. let's see how.
If I enter correct userName and password the query would look as shown below, which is correct.
user name = scott
password = tiger
Query - SELECT 1 FROM EMPLOYEE WHERE USER_NAME = 'scott' AND PASSWORD = 'tiger';
Suppose I enter below values
user name = scott
password = ' OR '1' = '1
Now the query would be
Query - SELECT 1 FROM EMPLOYEE WHERE USER_NAME = 'scott' AND PASSWORD = '' OR '1' = '1';
Look at the OR condition. Now the above query will fetch result and user will be allowed to enter the system without password.
To solve the above issue we can use Parameterized Queries in JDBC it's called as PreparedStatement. Parameterized Queries is a precompiled SQL statement. The SQL query uses a parameter instead of injecting the values directly into the query. Lets modify the above method with PreparedStatement.
private boolean isAuthenticate(String userName, String password) {
String query = "SELECT 1 FROM EMPLOYEE WHERE USER_NAME = ? AND PASSWORD = ?";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setString(1,userName);
pstmt.setString(2,password);
ResultSet rset = pstmt.executeQuery();
if (rset.next()) {
return true;
}
else {
return false;
}
}
Prepared statements are the ability to set up a statement once, and then execute it many times with different parameters. They are designed to replace building ad hoc query strings, and do so in a more secure and efficient manner. To look at complete JDBC Prepared statements example, click here.
