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

2 Comments to "Quoting text JavaScript/Python style"

  1. Daniël van Eeden wrote:

    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…

  2. Roland Bouman wrote:

    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)

Leave Your Comment

 
Powered by Wordpress and MySQL. Theme by openark.org