SQL query gives an error, but looks fine

Question

So, here's the query (copied and pasted from browser, echoed directly from the php code.)

INSERT INTO Azioni_report (Chiusa, Data, Descrizione, ID_report, IntEst, Tipo, Responsabile) 
VALUES (0, CONVERT(date, '2-12-2014', 105), 'verifica dell''efficacia', 1049, 1, 2, 12)

If I run it from my "test page", it works fine. When I run the exact same query in the page where I need it, it gives this error:

Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 102 [code] => 102 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'efficacia'. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'efficacia'. ) ) 1

I'm using SQL server.

This `'verifica dell''efficacia'` you have 2x `'` one beside the other. What do you want to do, add the apostrophe/quote? — Funk Forty Niner, Dec 02 '14 at 14:39
Try `'verifica dell'\'efficacia'` or `'verifica dell\'efficacia'` — Funk Forty Niner, Dec 02 '14 at 14:40
I tried that in my test page, and just as I thought it inserts "verifica dell\'efficacia" into the database. — Andrea, Dec 02 '14 at 14:42
I made an edit to my comment above, adding another. Reload it. Being `'verifica dell\'efficacia'` — Funk Forty Niner, Dec 02 '14 at 14:42
Instead of using a sql injection vulnerable approach in your code you should parameterize your query. This will protect your system and fix this problem for you. — Sean Lange, Dec 02 '14 at 14:43
Use sql-parameters. @Fred-ii-: you [escape an apostrophe](http://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sql-server) by doubling them. — Tim Schmelter, Dec 02 '14 at 14:43
Just as Sean says. Or you can use `stripslashes($var)` then `mysqli_real_escape_string($con,$var)`. — Funk Forty Niner, Dec 02 '14 at 14:43
Both of them give errors. First one "Incorrect syntax near '\'." Second one "Incorrect syntax near 'efficacia'." and "Unclosed quotation mark after the character string ', 1049, 1, 2, 12)'." — Andrea, Dec 02 '14 at 14:45
@Sean Lange Ok, but still that doesn't explain why it runs fine in one page and gives error in another. — Andrea, Dec 02 '14 at 14:47
Probably because of the type of quotes which are encapsulating the query... — RichardBernards, Dec 02 '14 at 14:49
Now this is strange. I just noticed that, even if it kicks an error, the query works. I mean, the data gets into the database exactly as it is intended. How is it possible? — Andrea, Dec 02 '14 at 14:55
I couldn't say. I don't know enough about that API. I'm mostly an MySQL coder. — Funk Forty Niner, Dec 02 '14 at 15:00

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

1

There is a problem where you are not escaping your quotes properly. Escaping is done by prepending them with a backslash \ like so:

INSERT INTO Azioni_report 
    (Chiusa, Data, Descrizione, ID_report, IntEst, Tipo, Responsabile) 
VALUES
    (0, CONVERT(date, '2-12-2014', 105), 'verifica dell\'\'efficacia', 1049, 1, 2, 12);

To avoid this altogether, try reading up on PDO or mysqli and the parameterizing (or binding) of your queries in PHP:

PDO - PDO::prepare

mysqli - Prepared Statements

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 02 '14 at 14:48

RichardBernards

3,146
1
22
30

As I stated, I use SQL server, and escaping quotes is done by doubling them. – Andrea Dec 02 '14 at 14:50

SQL query gives an error, but looks fine

1 Answers1