Running an externally supplied SQL statement with special characters

[Ivan Kalinin]

Hello there, fellow developers!

We've recently run into a terrible problem.

A small tool uses SQLAlchemy to execute statements read from a text file against a database.

The trouble comes when that pre-defined statement has a colon symbol in the field value of a, say, INSERT statement.

Like as follows:
INSERT INTO my_test_table values (123, ':bar')

Running this statement with a plain session.execute(stmt) (where stmt contains a unicode string with full statement) causes a StatementError with a message like "A value is required for bind parameter u'bar'"

However, I'm certain that parameter placeholders should not be parsed from within string literals.

Is there a way to tell SA that this statement should not be analyzed for placeholders?

Thanks in advance for help and advice!

Best regards,
Ivan.

[Michael Bayer]

the string passed to session.execute() is wrapped with a text() construct, which does parse for bound parameters so that they may be type-processed and converted to the representation expected by the DBAPI (which is usually not the colon style).    This parsing is pretty simplistic and does not expect that a quoted value would be directly embedded in the statement.  there’s no escaping for those at the moment, so you have to skip the text() part here.   To send a raw statement to the DBAPI layer without any processing, use the Connection object directly, that is, send session.connection().execute(stmt).

[Michael Bayer]

sorry, I’m partially incorrect here, you should escape out that colon with a backslash:

>>> from sqlalchemy import text

>>> print text("INSERT INTO my_test_table values (123, '\\:bar')")

[Ivan Kalinin]

Actually, using the session.connection().execute did help!

Also, I think there is an option of creating a TextClause subclass with a different search regex that, for example, matches nothing. But it's a bit of an overkill, IMO.

[Michael Bayer]

yes, that workaround works, but much more simply, using a backslash in text() should work as well

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

[Ivan Kalinin]

The point is we get those SQL statements from an external source and we'd prefer not to modify them. I do understand its a rare use-case of SA, but having a DumbTextClause or an option regex parameter in TextClause constructor could help.

[Michael Bayer]

if you’re using the text() constructor directly anyway, you can just define a helper like this:

def unescaped_text(sql):

    return text(sql.sub(‘:’, ‘\\:’))