How to build query in Java to prevent SQL injection using prepared statement

I need to build a query in such a way as to prevent the possibility of an SQL injection attack.

I know of two ways to build a query.

String query = new StringBuilder("select * from tbl_names where name = '").append(name).append(';).toString();

String query = "select * from tbl_names where name = ? ";

In the first case, all I do is a connection.preparestatement(query)

In the second case I do something like:

PreparedStatement ps = connection.prepareStatement(query)
ps.setString(1,name);

I want to know what is the industry standard? Do you use the string append way to build the query and then prepare the statement or prepare the statement already and pass parameters later?

2
Leave a Reply

avatar
2 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
1 Comment authors
Jason Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
Jason
Guest

Your first fragment of code is unsafe and vulnerable to SQL injection. You should not use that form. To make your first fragment safe, you would need to manually escape the value to prevent SQL injection. That is hard to do correctly, and choosing the wrong way of handling values could potentially reduce performance depending on the underlying database (eg some database systems will not use an index if you supply a string literal for an integer column). The second fragment is the standard way. It protects you against SQL injection. Use this form. Using a prepared statement with parameter… Read more »

Jason
Guest

You could also use the [OWASP ESAPI library][1]. It includes validators, encoders and many other helpful things.
For example, you can do

ESAPI.encoder().encodeForSQL(Codec,input);

More codecs are under development. Currently, MySQL and Oracle are supported. One of those might be helpful in your case.