How to give entry in the where clause when we use it in webpages?

Question

According to the user's input I want to select the record from the database. This is my code:

<%
String jempid=request.getParameter("empid");
out.println(jempid);
int intempid=1223;
Connection conn=null;
String url="jdbc:mysql://localhost/employees";
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn=DriverManager.getConnection(url,"root","");
Statement st=conn.createStatement();
ResultSet rs=st.executeQuery("select * from empdetails where empnum=jempid");
%>

It throws the following error

javax.servlet.ServletException: java.sql.SQLException: Unknown column 'jempid' in 'where clause

Answer 1

That looks like too much code in a jsp page.... that said:

... empnum='"+jempid+"');" ......

And also when you are done be sure to close the db too

Answer 2

Try this:

ResultSet rs=st.executeQuery(
    "select * from empdetails where empnum=" + jempid);

You need to put the value of jempid into the string, not the text "jempid".

Answer 3

Here is your select statement:

select * from empdetails where empnum=jempid

jempid is hardcoded instead of being used as a variable. This will never work unless you change it to the variable value entered by the customer.

alter to:

"select * from empdetails where empnum=" + CleanseUserInput(jempid)

and you're good to go.

Answer 4

ResultSet 
  rs=st.executeQuery("select * from empdetails where empnum=" + jempid + ";")

Answer 5

Thanks a lot guys.. Ooooooops! what a silly mistake..

Answer 6

"select * from empdetails where empnum="+jempid

But you really want to protect this against SQL injections!

Answer 7

It's a bad idea to construct SQL using string concatenation - you're just opening yourself up to a SQL injection attack -- ESPECIALLY considering that you're getting the value of "empid" directly from the request. Yikes!

A better approach is use a parametrized query such as below:

PreparedStatement st=conn.prepareStatement("select * from empdetails where empnum=?");
st.setString(1, jempid);
ResultSet rs=st.executeQuery();

Also you should check that jempid is not null.

Answer 8

In contrast to other responses, why not use a PreparedStatement?

Basically, you'll have to code like this:

PreparedStatement st=conn.prepareStatement("select * from empdetails where empnum=?");
st.setString(1, jempId);
ResultSet rs=st.executeQuery();

The reason you should use PreparedStatements is because of SQL Injection issues. For example, using the code you posted in the question, a hacker can always type "1;delete * from empdetails" in the textbox from which you select jempid.

Thus the final query formed would be

select * from empdetails where empnum=1;delete * from empdetails

which would result in all data in empdetails getting deleted.

So always use PreparedStatements!!