SQL query syntax error with constructed query

Question

I'm getting the error "syntax error (missing operator) in query expression" using this code:

x = "John's";
OleDbCommand commandRdd = new OleDbCommand("Select rdd_tbl.Customer, rdd_tbl.Balance From rdd_tbl WHERE rdd_tbl.Customer ='" + x + "'", con);
OleDbDataReader readerRdd = commandRdd.ExecuteReader();

I know the problem in John >'< s symbol, but i don't know how to make it a part of the name .

And see http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work before you have worse than syntax errors. — John Saunders, Feb 28 '15 at 01:54

score 2 · Answer 1 · answered Feb 28 '15 at 01:29

2

Escape ' with a \. Like so.

x = "John\'s";
OleDbCommand commandRdd = new OleDbCommand("Select rdd_tbl.Customer, rdd_tbl.Balance From rdd_tbl WHERE rdd_tbl.Customer ='" + x + "'", con);
OleDbDataReader readerRdd = commandRdd.ExecuteReader();

Also, use SQL Parameters like @Plutonix has suggested, else you are vulnerable to SQL Injection / "Bobby Tables"

enter image description here

answered Feb 28 '15 at 01:29

Shiva

20,575
14
82
112

Any chance to do it in commandRdd (second) line? Because "x" it's data base, but i'll try it like emergency way. )) thanks. – Alex Che Feb 28 '15 at 01:33
Escaping sequences are DB-specific – Alejandro Feb 28 '15 at 14:33

Alejandro · Accepted Answer · 2015-03-04T01:50:56.217

You really never want to do it by replacing any special character, while it may work with simple cases there can be other reserved characters that may require handling. Also, the escape sequences are DB specific, so different engines may need different treatments (I assume the multi-DB thing just because you used the generic OleDb classes instead of a more specific family).

Escaping special chars is a good solution for scripts and quick and dirty code, but never do that in a real life program, as there is a better option.

The real solution are parameters. You just send the query text with a parameter placeholder instead of the real value and then supply the value afterwards, before running. A quick implementation could be something like this:

x = "John's";
OleDbCommand commandRdd = new OleDbCommand("Select rdd_tbl.Customer, rdd_tbl.Balance From rdd_tbl WHERE rdd_tbl.Customer = @customer", con);
commandRdd.Parameters.Add(new OleDbParameter("@customer", OleDbType.VarChar, 100, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, x));
OleDbDataReader readerRdd = commandRdd.ExecuteReader();

A few things to notice. First, the query text contains rdd_tbl.Customer = @customer, a fixed text independent of the actual value you want to submit. then it comes the parameter declaration, where the name, data type and actual value are specified. Be sure to fill all those details and match them to the underlying DB structure. The rest of the code is exactly the same.

This also has the added benefit of improving performance. Because of the query text being exactly the same, the query can often be cached and reused afterwards if submitted again, even if the parameter value changes (those values are not part of the query itself). If you need to run the same query a few times in a row with different values, this makes possible to use prepared statements, which again are precompiled queries that run subsequently with different values. Those possible optimizations are all allowed by the API, but the actual results will vary depending the DB engine.

After reading all information about Escaping quotes, I prefer this answer more. But anyway thanks to all for the answers. — Alex Che, Mar 03 '15 at 20:39

SQL query syntax error with constructed query

2 Answers2