CODEWITHSUNDEEP
javasqlkeywordquotes
CodeKingPlusPlus
CodeKingPlusPlus

Reputation: 16081

Java SQL quotes in query strings

I have the following code to executing a sql query:

query = String.format("select * from users where user_name = '%s';", results.getString(1));
results2 = mStmt.executeQuery(query);

One user_name in the dataset has value "O'brien". This ruins my query due to the single parenthesis in "O'brien. The query would become:

select * from users where user_name = 'O'brien'

What is the strategy to overcome this and not modify the data?

EDIT: The prepared statement does fix the single quote problem however that was only part of the problem. One of the string values I have contains the word "UNDER". which is a SQL keyword. For example I have a preparedStatement called insertAll with the query:

insert into names (id, val1, val2, val3, val4, tTime, val5) values (?, ?, ?, ?, ?, ?, ?)

Thanks for all the help!

Upvotes: 0

Views: 9154

Answers (2)

Anton Garcia Dosil
Anton Garcia Dosil

Reputation: 299

Set your parameters with prepareStatement. Your code is a SQL injection exploitable code from the book. http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

Upvotes: 5

Daniel Miladinov
Daniel Miladinov

Reputation: 1592

Why not use PreparedStatement instead, avoid the tedious and error-prone issue of escaping quotes in sql queries altogether?

The could would look something like this (sorry, I didn't actually run this, but you should get the idea): 

query = "select * from users where user_name = ?";
PreparedStatement p = new PreparedStatement(con, query);
p.setString(1, results.getString(1));
results2 = p.executeQuery();

Upvotes: 1

Related Questions