What is SQL Injection Attack ?

Once the user clicks on the “LOG IN” button, an API call to the backend server will be made with a payload containing the entered username & password. We directly look at server-side logic that validates entered credentials with the credentials in the database table.

public static void main(String[] args) {
String username = "john";
String password = "John@123";
//password = "John@1231' OR '1'='1"; // SQLi payload input
try {
int userid = validateCredentialsAndFetchUserId(username, password);
System.out.println("User is found. User Id is :" + userid);
} catch (InvalidUserException e) {
System.out.println("Wrong username or password entered. No user found.");
}
}

private static int validateCredentialsAndFetchUserId(String username, String password) {
Connection con = null;
Statement st = null;
ResultSet resultSet = null;

try {
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "mydbuser", "Mydbpassword@123");
st = con.createStatement();

// Below SQL fetches the user with given username and password combination
String sql = "select * from users where username='" + username + "' and password='" + password + "'";
System.out.println("SQL to be fired :"+sql);

resultSet = st.executeQuery(sql);
if (resultSet.next()) {
// We got the results. This means the user with given userid and password exist.
// There can be max 1 record with given combination.
// Now just return user id of the first result.
return resultSet.getInt("userid");
} else {
throw new InvalidUserException("Invalid user with username :" + username);
}

In the above code, we are building an SQL query in the below form.

select * from users where username='john' and password='John@123'

If this query returns at least 1 result, then it means entered credentials exist in the database and the user is valid. The query is built using the string concatenation technique as below-

String sql = "select * from users where username='" + username + "' and password='" + password + "'";

This code snippet is vulnerable to SQL Injection attacks. It is an example of Error based SQL Injection. Here, the user-provided input fields are passed as is to build a database query using the string concatenation technique. An attacker can embed an actual SQL statement in any of the input fields and our code will concatenate the user’s input as is to the final SQL query to be executed on the database server.

Let’s understand with some example input test cases. Note, that we have added some debug logs (using system.out).

Prevention Technique 2: Validate input parameters

To prevent SQL Injection, you need to validate the values that are provided by users in the input fields. For example, if the input field is the user’s mobile number (+91-90901234) then we know that values will be numeric only (with maybe one special character ‘-‘). If we find any other character, then we can safely throw an error. If such a validation is done for each and every input field, then we will never let the attacker’s input reach our database queries.