How to escape semicolons in SQL queries

Hi!

I was wondering if it’s possible to escape semicolons ( ; ) somehow in SQL queries.

I tried sending an SQL query to my DB which contained a semicolon in a string field - the query failed (because the semicolon normally ends an statement). Escaping via single quote also doesn’t work…

PS:
Please no tips that will only work on MySQL like escaping via \ or something - thanks.

Originally posted by KillaByte
[B]Hi!

I was wondering if it’s possible to escape semicolons ( ; ) somehow in SQL queries.

I tried sending an SQL query to my DB which contained a semicolon in a string field - the query failed (because the semicolon normally ends an statement). Escaping via single quote also doesn’t work…

PS:
Please no tips that will only work on MySQL like escaping via \ or something - thanks. [/B]

Are you sure it’s the semicolon and not a single quote problem? If the semicolon is in the middle of the string, it shouldn’t matter. My guess is you’ve got a contraction or something in the string that is cutting off the string and making the semi-colon outside the string value.

No, it’s a semicolon problem but I noticed that if you do a query via PHP semicolons in the query are no problem, so the PHP<->DB interface seems to do the escaping automatically.

If I stick a semicolon into a query and execute this query with a DB-Tool (or the SQL-Console) it gives me an error - the same query works just perfect in PHP…

(as PHP isn’t affected by this, I don’t have any more problems but I would like to know what PHP does to the semicolon…)

Can I see the actual query? You’ve got me very curious now…

I tried something like

INSERT INTO test VALUES ('this is a test with a ; in it');

If I send this via SQL-Console or JDBC I get an error - via PHP (adoDB) there’s no problem (maybe PHP uses another query terminator)…

maybe “\;” via JDBC?

–Vinnie

Dunno - I use a JAVA-based SQL-Client - there escaping with \ doesn’t work.

Sounds like something freaky with the java client. That shouldn’t happen on a straight insert like that…

Thanks.

If I got some time I’ll check this by doing such a query directly via JDBC.

But when ;'s are valid in query-strings how does the db-engine know where the query ends (thinking of importing large SQL backups where thousands of queries are executed)?

Or has the engine to notice that the semicolon is enclosed in string delimiters?

We’ll see…

Originally posted by KillaByte
Or has the engine to notice that the semicolon is enclosed in string delimiters?
Bingo.