Clone wiki

sqlalchemy / UsageRecipes / BindsAsStrings

<div style="margin:5px 5px 20px 5px;padding:10px;border:1px dotted;background:#FFEEEE">
<h1 style="color: red;margin:0;padding:0">Warning</h1>

This recipe illustrates a method of generating SQL expressions with bound parameters rendered inline.   The method of escaping is <span style="color:red;font-weight:bold">not secure, incomplete, and for debugging only</span>.  It should <span style="color:red;font-weight:bold">never</span> be used in any production application.   Executing SQL statements with inline-rendered user values is <b>extremely</b> insecure - please read <a href="">SQL Injection</a> for a full background on SQL Injection attacks.  The SQLAlchemy project assumes <b>no responsibility</b> for damages related to the usage of this recipe!

Bind Parameters as Inline Strings

Starting with a typical statement:

from sqlalchemy import *

m = MetaData()

t = Table('t', m, 
    Column('x', Integer)

d = t.delete().where(t.c.x==5)

Method one. Monkeypatch compiler with one of its internal methods. This method is not secure for usage on all backends. YMMV.

d2 = d.compile()
d2.visit_bindparam = d2.render_literal_bindparam
print d2.process(d)

Method two. Use clause processing to replace instances of bindparam() with literal_column. This is more the "legit" way to do it. Again, the method as pictured below is not secure by any means.

from sqlalchemy.sql import visitors
from sqlalchemy.sql.expression import _BindParamClause

def replace_bind(b):
    if isinstance(b, _BindParamClause):
        # for demonstration only !  This line does **not** 
        # apply proper escaping, which varies by backend !
        # Please replace with proper escaping/quoting !
        return literal_column("%r" % b.value)

d3 = visitors.replacement_traverse(d, {}, replace_bind)

print d3