Michael Bayer wrote:
> Christoph Zwerschke wrote:
>> Michael Bayer wrote:
>>>
>>> once you have your connection, you can just execute:
>>> connection.execute("alter user foo set password='hoho'")
>>
>> how can I validate they have no illegal characters and escape
>> such chars so they cannot do anything evil on the database (the
>> string "hoho" must not contain an apostrophe; the string "foo"
>> must not contain a semicolon etc.)...
>
> you can use text() clauses with bind parameters encoded as
> ":somename"...and then feed those into connection.execute() along
> with a dict of values. check out the "sql construction" docs for
> examples.
Ok, thanks. "Text Blocks" were the cue I was missing when I looked into
the docs, but now I found it:
http://www.sqlalchemy.org/docs/sqlconstruction.myt#sql_textual
I think the problem was that the whole chapter has the heading
"Constructing SQL *Queries* via Python Expressions"
Since an SQL *query* usually only means "select" statements, I had not
suspected to find a solution for DDL in that chapter.
Maybe you can find a better chapter title here, because in the end of
the chapter you also talk about DML (Inserts, Updates, Deletes). Maybe
you should also extend that chapter with a subsection about DDL, since
text() blocks are only mentioned as a kind of "general select
statement", not as a way to formulate arbitrary DDL commands. Or you
split the chapter into two chapters, one for Queries, and an extra
chapter for DML and DDL.
One question is still left.
I can use a parameter for the password, like that:
db = create_engine(...)
alter_pwd = db.text("alter user foo with password :pwd",
bindparams=[bindparam('pwd')])
alter_pwd.execute(pwd="secret")
But I can't do the same for the user name( "foo"). I can't pass it as a
string parameter, because it would be enclosed in single quotes which is
the wrong syntax for SQL identifier. Actually, using parameters for SQL
identifiers is not supported by the DB API. And I also don't want to
insert it directly because that can be dangerous.
But maybe there is some SQLAlchemy mechanism that allows to inject a
variable SQL identifier into the query?
Such a function would need to check whether it is an ordinary SQL
identifier (only chars, digits, underscore etc.) or a delimited
identifier (can contain other chars). In the latter case, when the
identifier has mixed case or contains other chars, then it should be
automatically enclosed in double quotes, and quotes themselves need to
be escaped (doubled).
I suppose there is already something like that somewhere in SQLAlchemy
that could be used here.
-- Christoph
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/sqlalchemy
-~----------~----~----~----~------~----~------~--~---
