Unknown column 'sunny' in where clause

Question

  <body>
    <%  String name=session.getAttribute("user").toString();    %>

 <br>

  <%@ page import="java.sql.*" %>
  <%
   try
  {
   Class.forName("com.mysql.jdbc.Driver").newInstance();
   java.sql.Connection 
   con=DriverManager.getConnection
   ("jdbc:mysql://localhost:3306/pbl","root","");
   Statement st=con.createStatement();
   String q="select pcode ,pname,pprice from car where us="+name;
   ResultSet rs = st.executeQuery(q) ;
 %>

error: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'by name' at line 1

There has to be a clean dupetarget for this. If not, this can become the clean dupetarget. — T.J. Crowder, Mar 23 '19 at 10:56
Possible duplicate of: https://stackoverflow.com/questions/419021/how-does-javas-preparedstatement-work ... but I won't close, because every query is different. — Tim Biegeleisen, Mar 23 '19 at 11:02

T.J. Crowder · Answer 1 · 2019-03-23T10:58:23.910

i guess the problem in this line String q="select pcode,pname,pprice from car where us="+name;

Yes, because the name (sunny, apparently) it isn't in SQL quotes, so it looks like a column reference.

NEVER use string concatenation to add values to SQL queries. Use prepared statements instead:

PreparedStatement ps = con.prepareStatement("select pcode ,pname,pprice from car where us = ?");
ps.setString(1, name);
ResultSet rs = ps.executeQuery();

That way, the information is handled as data, not as SQL (properly escaped if necessary, etc.). Things to notice there:

You use ? where the values go. You don't put ? in quotes or anything, even when you're going to use a string for the value.
You get a prepared statement by calling prepareStatement on the connection.
You don't pass a string into executeQuery. (This is important, because sadly you can pass a string into executeQuery on a PreparedStatement, which bypasses the whole point of using prepared statements; it should have been defined to cause an exception, but sadly it it wasn't.)

Let me introduce you to my friend Bobby:

@TJCrowder Actually, when you pass a query string to `executeQuery` you are actually calling `Statement#executeQuery`, which is not a prepared statement method. — Tim Biegeleisen, Mar 23 '19 at 12:16
@TimBiegeleisen - Right. That's why I said not to do that. (Because sadly, `PreparedStatement` doesn't override `executeQuery(String)` and throw an exception, as I believe it ought to have done.) — T.J. Crowder, Mar 23 '19 at 12:20

Unknown column 'sunny' in where clause

1 Answers1