Preventing SQL-Injection in a Java application

Sql-Injection is a technique to inject (or execute) SQL commands within an application (database). It is mainly used to gain access to a databases content for which one has no authorization. The method injects SQL commands by providing input to a SQL statement which contains SQL meta-characters. Example statement: <strong>select * from user where username='input'</strong> SQL injected input:<strong>user' OR '1'='1</strong> The final statement:<strong>select * from user where username='user' OR '1'='1'</strong> Since 1 always equals 1, this statement would return all the users in the database! Depending on the SQL command, an attacker could insert, modify, extract and delete data in the database.

Taggings:

Submitted by Johannes Novotny on Sun, 11/14/2010 - 22:17
1 answer

Prepared Statements for preventing SQL-Injection in Java

To prevent a SQL-Injection, one should use prepared statements instead of normal statements. Why? Because a variable's SQL-metacharacters, passed as arguments to prepared statements, will automatically be escaped by the JDBC driver.

Example:
String selectStatement = "SELECT * FROM User WHERE userId = ? ";
PreparedStatement prepStmt = con.prepareStatement(selectStatement);
prepStmt.setString(1, userId);
ResultSet rs = prepStmt.executeQuery();

Incorrect usage of prepared statements can render their protective aspect inert.

Example:
String strUserName = request.getParameter("Txt_UserName");
PreparedStatement prepStmt = con.prepareStatement("SELECT * FROM user WHERE userId = '+strUserName+'");

Submitted by Johannes Novotny on Sun, 11/14/2010 - 22:47