[Django]-How to quote a string value explicitly (Python DB API/Psycopg2)

I guess you’re looking for the mogrify function.

Example:

>>> cur.mogrify("INSERT INTO test (num, data) VALUES (%s, %s)", (42, 'bar'))
"INSERT INTO test (num, data) VALUES (42, E'bar')"

You should try to avoid doing your own quoting. Not only will it be DB-specific as people have pointed out, but flaws in quoting are the source of SQL injection bugs.

If you don’t want to pass around queries and values separately, then pass around a list of the parameters:

def make_my_query():
    # ...
    return sql, (value1, value2)

def do_it():
    query = make_my_query()
    cursor.execute(*query)

(I probably have the syntax of cursor.execute wrong) The point here is that just because cursor.execute takes a number of arguments, that doesn’t mean you have to handle them all separately. You can deal with them as one list.

This’ll be database dependent (iirc, mysql allows \ as an escape character, while something like oracle expects quotes to be doubled: 'my '' quoted string').

Someone correct me if i’m wrong, but the double-quoting method is the standard method.

It may be worth looking at what other db abstraction libraries do (sqlalchemy, cx_Oracle, sqlite, etc).

I’ve got to ask – why do you want to inline the values instead of bind them?

Your code snippet would get just like this, according to psycopg extension docs

from psycopg2.extensions import adapt

value = "Unsafe string"
query = "SELECT * FROM some_table WHERE some_char_field = %s;" % \
    adapt(value).getquoted()
cursor.execute( query ) # value will be correctly quoted, too

The getquoted function returns the value as a quoted and escaped string, so you could also go: "SELECT * FROM some_table WHERE some_char_field = " + adapt(value).getquoted() .

This is going to be DB dependent. In the case of MySQLdb, for example, the connection class has a literal method that will convert the value to the correct escaped representation for passing to MySQL (that’s what cursor.execute uses).

I imagine Postgres has something similar, but I don’t think there is a function to escape values as part of the DB API 2.0 spec.

I don’t think you give any sufficient reasoning behind your avoidance to do this The Right Way. Please, use the APi as it is designed and don’t try so hard to make your code less readable for the next guy and more fragile.

PyPika in another good option for building SQL statements. Usage example (based on an example on the project’s homepage):

>>> from pypika import Order, Query
>>> Query.from_('customers').select('id', 'fname', 'lname', 'phone').orderby('id', order=Order.desc)
SELECT "id","fname","lname","phone" FROM "customers" ORDER BY "id" DESC

If you use django you might want to use the quoting function which is automatically adapted to the currently configured DBMS :

from django.db import backend
my_quoted_variable = backend.DatabaseOperations().quote_name(myvar)

import re

def db_quote(s):
  return "\"" + re.escape(s) + "\""

can do the job of simple quoting that works at least with MySQL. What we really need, though is cursor.format() function that would work like cursor.execute() except it would return the resulting query instead of executing it. There are times when you do not want the query to be executed quite yet – e.g you may want to log it first, or print it out for debugging before you go ahead with it.