Having mySQL error, unknown column where clause

Question

I am currently having a problem selecting a specific item from mySQL database. My program is designed to pass a parameter from an android application to a servlet, which then queries the database.

However, an error appears in the console window: Unknown column "0102310c24" in the "where" section

There is only an error when the value I want to select contains a letter.

When I request this number 0106172459, the data I want is returned without problems.

Below is my servlet.

import java.io.*; import java.util.*; import javax.sql.*; import javax.servlet.*; import javax.servlet.http.*; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DBServlet extends HttpServlet { public void service(HttpServletRequest request,HttpServletResponse response) throws IOException, ServletException{ response.setContentType("text/html"); PrintWriter out = response.getWriter(); String x=request.getParameter("item"); Connection con = null; Statement stmt = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); con =DriverManager.getConnection ("jdbc:mysql://IP/databasename", "username", "password"); stmt = con.createStatement(); rs = stmt.executeQuery("SELECT * FROM items WHERE item="+x); // displaying records while(rs.next()){ out.print(rs.getObject(1).toString()); out.print(","); out.print(rs.getObject(2).toString()); out.print(","); out.print(rs.getObject(3).toString()); out.print(","); out.print(rs.getObject(4).toString()); out.print(","); out.print(rs.getObject(5).toString()); out.print(","); out.print(rs.getObject(6).toString()); out.print(","); out.print(rs.getObject(7).toString()); out.print(","); out.print(rs.getObject(8).toString()); out.print(","); out.print("\n"); } } catch (SQLException e) { throw new ServletException("Servlet Could not display records.", e); } catch (ClassNotFoundException e) { throw new ServletException("JDBC Driver not found.", e); } finally { try { if(rs != null) { rs.close(); rs = null; } if(stmt != null) { stmt.close(); stmt = null; } if(con != null) { con.close(); con = null; } } catch (SQLException e) {} } out.close(); } } 

and I use this to send the value to the servlet.

 List<NameValuePair> nameValuePair = new ArrayList<NameValuePair>(); nameValuePair.add(new BasicNameValuePair("item","List<NameValuePair> nameValuePair = new ArrayList<NameValuePair>(); nameValuePair.add(new BasicNameValuePair("item","010238a2cc"));")); 

I will be grateful if anyone can help

Answer 1

The value of x must be enclosed in quotation marks, or it is assumed that a non-numeric value refers to a column, not a string literal.

 rs = stmt.executeQuery("SELECT * FROM items WHERE item='"+x + "'"); 

Note that your script is vulnerable to SQL injection, since the value of x not escaped, but obtained from Request . It would be better to use the prepared expression here.

Answer 2

Try using PreparedStatement .

 PreparedStatement st = con.prepareStatement("SELECT * FROM items WHERE item=?"); st.setString(1,x); rs = st.executeQuery(); 

Answer 3

If the element is of type String, you need to enclose it in quotation marks. Even better, you should use PreparedStatement to execute the request.

Answer 4

If x is an integer, use the following code: rs = stmt.executeQuery ("SELECT * FROM items WHERE item =" + x); If x is a string, use the following code: rs = stmt.executeQuery ("SELECT * FROM items WHERE item = '" + x "'");