CODEWITHSUNDEEP
java
user569322
user569322

Reputation:

Java JDBC Query with variables?

String poster = "user";

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM `prices` WHERE `poster`="+poster);

This does not work.Any tips or tricks would be appreciated.

Upvotes: 4

Views: 36911

Answers (3)

Óscar López
Óscar López

Reputation: 235984

Try surrounding the poster variable with single quotes, like this:

ResultSet rs = stmt.executeQuery("SELECT * FROM `prices` WHERE `poster`='"+poster+"'");

That's because SQL expects strings to be surrounded by single quotes. An even better alternative would be to use prepared statements:

PreparedStatement stmt = con.prepareStatement("SELECT * FROM `prices` WHERE `poster` = ?");
stmt.setString(1, poster);
ResultSet rs = stmt.executeQuery();

It's recommended using PreparedStatement since the way you are currently building the query (by concatenating strings) makes it easy for an attacker to inject arbitrary SQL code in a query, a security threat known as a SQL injection.

Upvotes: 6

Kevin Webber
Kevin Webber

Reputation: 125

The Statement interface only lets you execute a simple SQL statement with no parameters. You need to use a PreparedStatement instead. 

PreparedStatement pstmt = con.prepareStatement("
                          select * from 
                          prices where 
                          poster = ?");

pstmt.setString(1, poster);
ResultSet results = ps.executeQuery();

Upvotes: 0

paulsm4
paulsm4

Reputation: 121609

1) In general, to "parameterize" your query (or update), you'd use JDBC "prepared statements":

2) In your case, however, I think all you need to do is add quotes (and lose the back-quotes):

// This is fine: no back-quotes needed
ResultSet rs = stmt.executeQuery("SELECT * FROM prices");


// Since the value for "poster" is a string, you need to quote it:
String poster = "user";
Statement stmt = con.createStatement();
ResultSet rs = 
  stmt.executeQuery("SELECT * FROM prices WHERE poster='" + poster + "'");

Upvotes: 0

Related Questions