2

Using Python's triple-quoted strings I can define strings containing ', backticks, or " without having to care about it:

hello = """    
            This string is bounded by triple double quotes (3 times ").
        Unescaped newlines in the string are retained, though 
        it is still possible to use all normal escape sequences.

            Whitespace at the beginning of a line is
        significant.  

            This string ends in a newline.
        """

In PHP I could use heredoc (<<<):

$str = <<<EOD
Example of string
spanning multiple lines
using heredoc syntax.
EOD;

How can I do the same in MySQL? Right now I have to make sure I escape the character I use to delimitate, e.g. if I use ' as delimiter I have to use \' in my string:

UPDATE article
SET article_text ='
Extra-virgin olive oil is a staple in any chef\'s kitchen.
'

I would like to be able something like

UPDATE article
SET article_text ='''
Extra-virgin olive oil is a staple in any chef's kitchen.
'''
Dharman
  • 30,962
  • 25
  • 85
  • 135
Franck Dernoncourt
  • 77,520
  • 72
  • 342
  • 501

1 Answers1

3

You can also use two single quotes in a row ('') instead of backslash-escaping the single quote (\').

Apart from double single quotes, there isn't an easier way, as far as I know. There is no equivalent to """. Which is sad.

But really your best bet (if you want to save yourself time) as mentioned here is not to use any SQL-based solution at all; most commonly databases are accessed by programs written in another language, or by SQL scripts generated by programs written in another language.

I haven't run into a single programming language that doesn't support prepared statements and have at least some SQL-escaping functions for convenience.

Community
  • 1
  • 1
Parthian Shot
  • 1,390
  • 1
  • 13
  • 24
  • The main reasons for prepared statements and escaping, of course, are security. It's inefficient to use them on constant strings in your code. _However_ it's not inefficient, if you need to escape a lot of text, to run that text through an escaping function, print the result to stdout, and then copy-paste the result back into your code. – Parthian Shot May 27 '14 at 20:50