Can't escape semicolons in a MySQL UPDATE statement

Question

I am currently having an issue with a MySQL UPDATE statement I am working with. For some reason I am unable to escape the semicolons in the statement.

If I remove all the semi-colons from the statement it works fine, so I know it has something to do with escaping the semi-colons.

I've tried the standard method of escaping them by preceding them with a \, however that does nothing.

I've also tried switching/swapping out all the single quotes for double quotes, and double quotes for single quotes, to reverse the ordering of them, which also does nothing.

Here is the statement I am trying to execute:

UPDATE email_campaign_template 
                SET content='<style>
/* General */
body {
    font-family: "Open Sans", sans-serif;
    color: #fdfcfc;
}
.container {
    max-width: 660px;
}
* {
  -webkit-box-sizing: border-box;
  -moz-box-sizing: border-box;
  box-sizing: border-box;
}
</style>
'
                WHERE label='Property Flyer 01' 
                AND email_campaign_id = '10';

This generates the following error, which is referring to the first semicolon in the statement:

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''<style> /* General */ body { font-family: "Open Sans", sans-serif' at line 2

How can I successfully escape the semi-colons in order to be able to execute this RAW MySQL statement?

you mention php. Please show the full php instruction containing this query. — Sebas, Aug 04 '15 at 18:17
Use a Heredoc. More info here: http://php.net/manual/en/language.types.string.php — mongy910, Aug 04 '15 at 18:19
Thanks for pointing that out. Actually at this point I've isolated the issue down to something outside of any PHP, to just the query statement itself. I will remove the PHP tag. — VerySeriousSoftwareEndeavours, Aug 04 '15 at 18:20
which php function are you using to run the query? pdo? something else? — Brian Glaz, Aug 04 '15 at 18:21
The error message apparently complains about the line feed. Why do you mention semicolons? Said that, do yourself a favour and don't escape stuff manually: use prepared statements and let PHP do the job for you. — Álvaro González, Aug 04 '15 at 18:25
Please refrain from suggesting solutions using PHP. This is a raw MySQL statement. I understand how to escape strings in PHP, but what I am trying to figure out is why this raw MySQL statement does not work. I've tried executing it using Navicat, Workbench, and even command line. — VerySeriousSoftwareEndeavours, Aug 04 '15 at 19:43
@McWayWeb - I'm puzzled. I cannot reproduce your syntax error with regular clients (command-line, Workbench or HeidiSQL) but it happens with sqlfiddle.com—yet SQL Fiddle has a custom SQL parser that's not reliable. Even then, it doesn't complain about semicolons, which do not have any special meaning inside SQL strings. — Álvaro González, Aug 06 '15 at 09:32
You and be both. Still not quite sure why it was happening. I ended up just preparing and executing it through PHP, which worked fine. But I still never understood why the SQL was giving me an issue in the first place . — VerySeriousSoftwareEndeavours, Aug 06 '15 at 14:53

score 2 · Accepted Answer · answered Mar 02 '16 at 02:52

2

Use CHAR(59) whenever you want to put a semicolon in a string.

answered Mar 02 '16 at 02:52

Rob Cole

721
1
6
16

score -1 · Answer 2 · edited Mar 30 '18 at 15:42

-1

You can sanitize your query with mysqli_real_escape_string()

See documentation for implementation. http://php.net/manual/en/mysqli.real-escape-string.php

edited Mar 30 '18 at 15:42

nyedidikeke

6,899
7
44
59

answered Aug 04 '15 at 18:21

jhegeman2

59
6

score -2 · Answer 3 · answered Aug 04 '15 at 18:22

-2

You can use a Heredoc to escape large segments of code or text

  $text = <<<HEREDOC
'<style>
    /* General */
    body {
        font-family: "Open Sans", sans-serif;
        color: #fdfcfc;
    }
    .container {
        max-width: 660px;
    }
    * {
      -webkit-box-sizing: border-box;
      -moz-box-sizing: border-box;
      box-sizing: border-box;
    }
    </style>
HEREDOC;

      UPDATE email_campaign_template 
                    SET content= '$text'
                    WHERE label='Property Flyer 01' 
                    AND email_campaign_id = '10';

answered Aug 04 '15 at 18:22

mongy910

537
1
5
16

I didn't downvote you, but it may be because your code is really no different to the OPs "pseudo-code" and you aren't actually "escaping" anything. – MrWhite Aug 04 '15 at 20:59
I added a heredoc which should escape everything enclosed within – mongy910 Aug 04 '15 at 22:47
Heredoc syntax is simply another way to delimit a string literal, it does not "escape" anything. In order to _escape_ the string (to prevent SQL-injection) you need to either call [`mysqli_real_escape_string()`](http://php.net/manual/en/mysqli.real-escape-string.php), or (preferably) use [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). – MrWhite Aug 04 '15 at 23:06
He isn't asking how to escape it to prevent sql injection though, he's asking how to make his query not give him an error. – mongy910 Aug 04 '15 at 23:44
The point is that heredoc syntax does nothing to help this situation. Escaping to prevent "SQL injection" or escaping to prevent "a SQL error" is the same thing. – MrWhite Aug 05 '15 at 07:53

Can't escape semicolons in a MySQL UPDATE statement

3 Answers3