PreparedStatements are the way to go, because they make SQL injection impossible. Here's a simple example taking the user's input as the parameters:
public insertUser(String name, String email) {
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = setupTheDatabaseConnectionSomehow();
stmt = conn.prepareStatement("INSERT INTO person (name, email) values (?, ?)");
stmt.setString(1, name);
stmt.setString(2, email);
stmt.executeUpdate();
}
finally {
try {
if (stmt != null) { stmt.close(); }
}
catch (Exception e) {
// log this error
}
try {
if (conn != null) { conn.close(); }
}
catch (Exception e) {
// log this error
}
}
}
No matter what characters are in name and email, those characters will be placed directly in the database. They won't affect the INSERT statement in any way.
There are different set methods for different data types -- which one you use depends on what your database fields are. For example, if you have an INTEGER column in the database, you should use a setInt method. The PreparedStatement documentation lists all the different methods available for setting and getting data.
PreparedStatements are the way to go, because they make SQL injection impossible. Here's a simple example taking the user's input as the parameters:
public insertUser(String name, String email) {
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = setupTheDatabaseConnectionSomehow();
stmt = conn.prepareStatement("INSERT INTO person (name, email) values (?, ?)");
stmt.setString(1, name);
stmt.setString(2, email);
stmt.executeUpdate();
}
finally {
try {
if (stmt != null) { stmt.close(); }
}
catch (Exception e) {
// log this error
}
try {
if (conn != null) { conn.close(); }
}
catch (Exception e) {
// log this error
}
}
}
No matter what characters are in name and email, those characters will be placed directly in the database. They won't affect the INSERT statement in any way.
There are different set methods for different data types -- which one you use depends on what your database fields are. For example, if you have an INTEGER column in the database, you should use a setInt method. The PreparedStatement documentation lists all the different methods available for setting and getting data.
The only way to prevent SQL injection is with parameterized SQL. It simply isn't possible to build a filter that's smarter than the people who hack SQL for a living.
So use parameters for all input, updates, and where clauses. Dynamic SQL is simply an open door for hackers, and that includes dynamic SQL in stored procedures. Parameterize, parameterize, parameterize.
Right, prepared statement query parameters can be used only where you would use a single literal value. You can't use a parameter for a table name, a column name, a list of values, or any other SQL syntax.
So you have to interpolate your application variable into the SQL string and quote the string appropriately. Do use quoting to delimit your table name identifier, and escape the quote string by doubling it:
java.sql.DatabaseMetaData md = conn.getMetaData();
String q = md.getIdentifierQuoteString();
String sql = "SELECT MAX(AGE) FROM %s%s%s";
sql = String.format(sql, q, tablename.replaceAll(q, q+q), q);
For example, if your table name is literally table"name, and your RDBMS identifier quote character is ", then sql should contain a string like:
SELECT MAX(AGE) FROM "table""name"
I also agree with @ChssPly76's comment -- it's best if your user input is actually not the literal table name, but a signifier that your code maps into a table name, which you then interpolate into the SQL query. This gives you more assurance that no SQL injection can occur.
HashMap h = new HashMap<String,String>();
/* user-friendly table name maps to actual, ugly table name */
h.put("accounts", "tbl_accounts123");
userTablename = ... /* user input */
if (h.containsKey(userTablename)) {
tablename = h.get(userTablename);
} else {
throw ... /* Exception that user input is invalid */
}
String sql = "SELECT MAX(AGE) FROM %s";
/* we know the table names are safe because we wrote them */
sql = String.format(sql, tablename);
Not possible. Best what you can do is to use String#format().
String sql = "SELECT MAX(AGE) FROM %s";
sql = String.format(sql, tablename);
Note that this doesn't avoid SQL injection risks. If the tablename is a user/client-controlled value, you'd need to sanitize it using String#replaceAll().
tablename = tablename.replaceAll("[^\\w]", "");
Hope this helps.
[Edit] I should add: do NOT use this for column values where you can use PreparedStatement for. Just continue using it the usual way for any column values.
[Edit2] Best would be to not let the user/client be able to enter the tablename the way it want, but better present a dropdown containing all valid tablenames (which you can obtain by DatabaseMetaData#getCatalogs()) in the UI so that the user/client can select it. Don't forget to check in the server side if the selection is valid because one could spoof the request parameters.