SQL injection

SQL injection, as one of the most common web security vulnerabilities, is a code injection technique that aims to destroy your database. This occurs by inserting (i.e. injecting) malicious sql code into a sql query, typically via a web page input, which is then run by the application against the database.

Let us say we have a simple login form that asks the user to enter a uer name. The entered user name is then made sure to exist in Users table in the database. If it does, the user is successfully logged in and greeted with a welcome message. Otherwise, the user is prompted again with the login form. The following is the main function that must validate the user: it returns true if the username is found in the Users table, false otherwise.

@Override
public boolean validateUser(User user) throws SQLException {
String sql = "SELECT * FROM Users WHERE name= \"" + user.getUserName() + "\";";
final String URI = "jdbc:sqlite:./DB.db";
java.sql.Connection conn = DriverManager.getConnection(URI);
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);
if (rs.next()) {
return true;
}
return false;
}

And here is the code that goes in the main function:

public static void main(String[] args) {
port(7000);
staticFiles.location("/public");
get("/", (req, res) -> {
Map<String, Object> model = new HashMap<String, Object>();
if (req.cookie("username") != null) {
model.put("username", req.cookie("username"));
}
return new ModelAndView(model, "public/index.vm");
}, new VelocityTemplateEngine());
post("/", (req, res) -> {
Map<String, Object> model = new HashMap<String, Object>();
String userName = req.queryParams("user");
String pass = req.queryParams("pass");
User user = new User(userName, pass);
if (new SQLUsersDao().validateUser(user)) {
res.cookie("username", URLEncoder.encode(userName, "UTF-8"));
}
res.redirect("/");
return "";
});
}

And this is what goes in index.vm:

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Log In!</title>
</head>
<div>
#if($username)
<h1>Welcome $username</h1>
<p><div id="keywordDisplay"></div></p>
<label for="user">Username: </label>
<input type="text" name="keyword" placeholder="Search Keyword" id="keyword" required/>
<input type="button" value="search" id="searchbtn" />
#else
<h1>Please Login</h1>
<div class="divContents indexLinkWrapper">
<form action="/" method="post" id="login">
<label for="user">Username: </label>
<input type="text" name="user" placeholder="User Name" id="user" required/>
<br/>
<input type="submit" value="Sign In!" id="loginbtn" />
</form>
</div>
#end
</body>
</html>

This all of course is very naive, but it might seem to work fine. In fact, it would work fine under normal conditions but there is actually a big vulnerability that could be easily exploited by a malicious user. Try entering name" OR "1"="1 in the user textbox in the login form. That will let you log in no matter what you put in place of name. The reason is name" OR "1"="1 will be the user name that gets direcly inserted in the sql query in validateUser function resulting in the query: SELECT * FROM Users WHERE name="name" OR "1"="1". Note that this query matches every single row in the Users table leading the validateUser function to return true no matter what to replace "name" with as long as " OR "1"="1 is appended to it.

Prevention

SQL injection is typically preventable easily by input sanitization:

Input Sanitization

Input Sanitization is the act of checking and modifying the user input (if needed) to prevent it from exploiting security holes.

It is certainly NOT a good idea to collect user input and insert it into a sql query (that gets executed against our database) without doing any sort of checks/validations on it. It is therefore sensible to make the following modification to validateUser function:

@Override
public boolean validateUser(User user) throws SQLException {
String sql = "SELECT * FROM Users WHERE name = ?";
final String URI = "jdbc:sqlite:./DB.db";
java.sql.Connection conn = DriverManager.getConnection(URI);
PreparedStatement st = conn.prepareStatement(sql);
st.setString(1, user.getUserName());
ResultSet rs = st.executeQuery();
if (rs.next()) {
return true;
}
return false;
}

Note that we use PreparedStatement and setString method to insert the user name into the query. The difference is that setString sanitizes the input before inserting it into the sql query, preventing injection of harmful data.

Edit this page