On Sep 17, 2013, at 6:32 PM, Samer Atiani <sati...@gmail.com> wrote:
> Hello,
>
> I'm trying to write a custom REPLACE expression that compiles differently on
> MySQL vs sqlite, namely it would look like REPLACE INTO in mysql and INSERT
> OR REPLACE in sqlite. I implemented it like so:
>
> class ReplaceInto(ValuesBase):
> def __init__(self, table, values=None):
> super(ReplaceInto, self).__init__(table, values, None)
>
> @compiles(ReplaceInto)
> def visit_replace_into(element, compiler, **kw):
> return 'REPLACE INTO %s(%s) VALUES (%s)' % (
> compiler.process(element.table, asfrom=True),
> ', '.join(element.parameters.keys()),
> "'" + "', '".join(element.parameters.values()) + "'"
> )
>
> @compiles(ReplaceInto, 'sqlite')
> def visit_replace_into_sqlite(element, compiler, **kw):
> return 'INSERT OR REPLACE INTO %s(%s) VALUES (%s)' % (
> compiler.process(element.table, asfrom=True),
> ', '.join(element.parameters.keys()),
> "'" + "', '".join(element.parameters.values()) + "'"
> )
>
> But this seems brittle and prone to SQL injection attacks. I'm wondering if
> there is SQLAlchemy API I should be reusing in the visit_replace_into_sqlite
> methods to write this better?
that's a pretty good effort, what you need to do is generate bindparam()
objects on the fly for the values which you are sending in, then send those to
the compiler (e.g. like ", ".join(compiler.process(bindparam(colname, value)
for colname, value in zip(cols, values)) ). The compiler internally uses a
complex method called _get_colparams() to do this, though you should be able to
skip that here if you're just doing something simple.
>
> On a separate note, the above works when you do:
>
> r = ReplaceInto(table, {'some_colname', 'some_value'})
>
> But does not work if I do this:
>
> r = ReplaceInto(table).values(some_colname = 'some_value')
>
> Because in the latter case, element.parameters would be None in
> visit_replace_into, even though I'm inheriting from ValuesBase.
.values() does populate .parameters.... (sorry I'm not actually running code
here to analyze results more closely)
signature.asc
Description: Message signed with OpenPGP using GPGMail
