Error in SQL Syntax

Question

I am trying to connect to database in Java. It's a simple program.

Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/servlets","root","");
smt = con.createStatement();
query = "select pass from users where uname = "+uname;
System.out.println(query);
rs = smt.executeQuery(query);
if((rs.getString("pass"))==pass){
    out.println("correct pass...logged in..");
}
else {
    out.println("Incorrect pass...not logged in..");
}

But it says

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 '@gmail.com' at line 1

I am trying to verify the password for a particular email-id.

CHAR args have to be quoted with `'` or use bind parameters. Also, that's not how you check `String` equality. — Elliott Frisch, Mar 25 '16 at 16:10
You should escape `uname` with quotes in the query, or better yet, use `PreparedStatement` . — Arnaud, Mar 25 '16 at 16:11
Your next question is answered here: http://stackoverflow.com/q/513832 — BalusC, Mar 25 '16 at 16:20

score 5 · Accepted Answer · answered Mar 25 '16 at 16:12

5

At this line

query = "select pass from users where uname = "+uname;

You have not quoted the uname, so if the value is name@gmail.com this results in a syntax error. I.e. the actual statement being sent to the DB is

select pass from users where uname = name@gmail.com

which is invalid. You should be using PreparedStatement instead

query = "select pass from users where uname = ?";
PreparedStatement ps = con.prepareStatement(query);
ps.setString(1,uname);
ResultSet rs = ps.executeQuery();

answered Mar 25 '16 at 16:12

Jim Garrison

85,615
20
155
190

I know you probably just wanted to keep it simple, but if you need to teach about `PreparedStatement`, you should use the opportunity to also teach the use of try-with-resources. It doesn't really extend the example much, and it will lead readers in the right direction, preempting the next question about why resources get exhausted. – Andreas Mar 25 '16 at 16:19

score 1 · Answer 2 · answered Mar 25 '16 at 16:11

1

Replace this

        query = "select pass from users where uname = "+uname;

to

       query = "select pass from users where uname = "'+uname+'"  ";

or try

            query = "select pass from users where uname = ? ";

answered Mar 25 '16 at 16:11

manikant gautam

3,521
1
17
27

I see absolutely *no* good reason that OP query should not be using prepared statements with bind placeholders. We don't have any information about whether the contents of `uname` (the return from `uname.toString()` ) is verified to be *safe* to include in SQL text... "oh yes Little Bobby Tables we call him"... we cannot in good conscience recommend wrapping `uname` in single quotes and including it as part of the SQL text. (Perhaps the suggestion in this answer, adding single quotes outside of the string literals (what?) is an sly way of suggesting that this is *not* the right approach.) – spencer7593 Mar 25 '16 at 16:37

Error in SQL Syntax

2 Answers2