writing a SQL statement in Java

[adi501]

Hi.

I have written the following Sql statement in my java code.


String str = "SELECT ZipCode,State FROM zipcodes WHERE City = '+city+' AND State = '+state+'";
rsZipCodes = oStatement.executeQuery(str);


Now when i am running the above statement in my query browser i am getting the following sql statement.

SELECT ZipCode,State FROM zipcodes WHERE City ='+city+' AND State = '+state+';

In the above statement the city and state value is passed from the user.

But i need the below Sql statement to get the correct output.

SELECT ZipCode,State FROM zipcodes WHERE City = 'TAMPA' AND State ='FL';


Finally my problem is with dealing with the double codes and single codes in the WHERE clause.

Anybody know how to deal with this.
Thanks in Advance.

[shyam]

String str = "SELECT ZipCode,State FROM zipcodes WHERE City = '+city+' AND State = '+state+'";
rsZipCodes = oStatement.executeQuery(str);


String str = "SELECT ZipCode,State FROM zipcodes WHERE City = '"+city+"' AND State = '"+state+"'";

[brad211987]

Also look into using prepared statements, they are considered more secure and I find them easier to use.

[jerry62704]

You have to put strings in quotes. Where you have ('+city+') to concatenate the variable "city" to the string you are building, you need to tell the system that the value of "city" is a literal. Just add a quote before and after it inside of the rest of the string parts.

String str = "SELECT ZipCode,State FROM zipcodes WHERE City = '" + city + "' AND State = '" + state + "'";

making the first part:

"SELECT ZipCode, State FROM zipcodes WHERE City = '"
and the next part:
+ city + "' ...
ending the quotes around the variable. Same thing with the last variable:
"' AND State = '" + state + "'";

BTW, you might use prepared statements which eliminates the confusion of quotes on strings and dates, but if you understand them you don't need it. If you do use it, it would look like this:

PreparedStatement pstmt = this.conn.prepareStatement(SQLStrings.getInsertUserQuery());

pstmt.setString(1, this.loginForm.getJdbcUserName());
pstmt.setString(2, this.loginForm.getUserPassword());
pstmt.setString(3, this.loginForm.getUserID());
pstmt.executeUpdate();


Where the sql statement would look like this:
String insertUserQuery =
"insert into \"DHSDB2\".\"JV_PERSON\" " +
"(\"USERID\", \"PASSWORD\", \"NAME\") " +
"values(?, ?, ?)";


The "?" in the query are substituted to the numbered parameters in the prepared statement.