Quoting text JavaScript/Python style

November 15, 2011

Unless your MySQL is configured to use ANSI_QUOTES in sql_mode, you are able to quote your text in one of two forms: using single quotes or double quotes:

UPDATE world.Country SET HeadOfState = 'Willy Wonka' WHERE Code='USA'
UPDATE world.Country SET HeadOfState = "Willy Wonka" WHERE Code="USA"

This makes for JavaScript- or Python-style quoting: you quote by your needs. Say you have a text which includes single quotes:

It is what you read when you don't have to that determines what you will be when you can't help it. - Oscar Wilde

You wish to insert this text to some tables. You could go through the trouble of escaping it:

INSERT INTO quotes (quote, author) VALUES (
  'It is what you read when you don\'t have to that determines what you will be when you can\'t help it.', 'Oscar Wilde');

or you could just wrap it in double quotes:

INSERT INTO quotes (quote, author) VALUES (
  "It is what you read when you don't have to that determines what you will be when you can't help it.", 'Oscar Wilde');

I find this useful when using SQL to generate queries. Take, for example, eval() for MySQL: the statement:

CALL eval('select concat(\'KILL \',id) from information_schema.processlist where user=\'webuser\'');

is just so more easily written this way:

CALL eval("select concat('KILL ',id) from information_schema.processlist where user='webuser'");

I don't suggest one should use this method throughout her application code. Application code works great with auto-escaping string literals. But for the handy DBA or developer, who needs to work some quick queries by hand, this makes for an easier syntax to use.

tags: ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

  • String escaping between MySQL and PostgreSQL is unfortunately not very compatible. As of PostgreSQL 9.1 standard_conforming_strings is on by default. Which means you have to use E'some string with backslash escapes.' which is not supported in MySQL. So much for SQL standards and different implementations…

  • Actually, to the best of my knowledge, the standard way to escape single quotes is by using two single quotes. I don't think there are any other escapes

    (I should check what it says on characters like \t\f\r\n etc. I think you're supposed to enter them literal, as is in standard SQL)

 
Powered by Wordpress and MySQL. Theme by openark.org