Re: sqlite3 - adding tables and rows via parameters
Steve Holden
> Hi all,
> I would like to ask about the usage of sqlite3 in python, more
> specifically about a way to pass table
> or column names to a SQL commands using parameters.
> All examples I could find use
> the parameter substitution with "?" for values; is it possible the specify table and column names this way?
> e.g. something like
> curs.execute(u'INSERT OR REPLACE INTO %s(%s) VALUES (?)' % ("people",
> "email"), ("q...@asd.zx",))
> (And the similar cases e.g.: CREATE TABLE, ALTER TABLE ... ADD.)
> Unfortunately, I wasn't successful in replacing the string interpolation
> with the substitution notation; are there maybe any hints, how to do
> that? Or is it ok to use string interpolation here? (Are these parts of
> the SQL command vulnerable too?)
>
> What I am trying to achieve is to build a database dynamically - based
> on the input data the respective
> table would be chosen, and the appropriate columns created and filled with the content.
>
> Thanks in advance for any suggestions - and sorry if I missed something
> obvious...
> Vlasta
>
>
The thing that will stop you from using a tablename as an argument to a
parameterized query is that (the) front-ends (I am familiar with) don't
allow table names to be parameterized ...
The main points of parameterization are
1) To let the driver handle the creation of syntactically correct SQL
(thereby avoiding , e.g. SQL injection attacks) and
2) To allow the driver to get the back-end to optimize the query by
developing an execution plan into which the parameters can ve inserted
at run time, avoiding repeated recompilation of the same query. It's
this that stops most backends from allowing table names, since the
optimizations depend upon the table's characteristics and contents.
As far as ? vs %s goes, that depends on the particular module's
paramstyle. For sqlite3:
>>> import sqlite3
>>> sqlite3.paramstyle
'qmark'
>>>
so you just have to go with what it implements.
regards
Steve
--
Steve Holden +1 571 484 6266 +1 800 494 3119
Holden Web LLC http://www.holdenweb.com/
Steve Holden
>
> 2008/4/12, Steve Holden <st...@holdenweb.com <mailto:st...@holdenweb.com>>:
>
> Vlastimil Brom wrote:
> > Hi all,
> > I would like to ask about the usage of sqlite3 in python, more
> > specifically about a way to pass table
> > or column names to a SQL commands using parameters.
>
>
> parameterized query is that (the) front-ends (I am familiar with) don't
> allow table names to be parameterized ...
>
>
>
> regards
> Steve
> --
> Steve Holden +1 571 484 6266 +1 800 494 3119
> Holden Web LLC http://www.holdenweb.com/
>
>
> Thank you very much for the explanation Steve!
> I noticed the limitation, but wasn't sure, if if I wasn't missing
> anything, as I don't have many experiences with databases (now I am
> actually trying to reimplement, what was previously managed to with
> nested dictionaries - hence I don't think, something more robust than
> sqlite is appropriate).
> But now I am not sure; are there any (security
> ...) risks of using string interpolation for table and column names in the SQL commands? Or
> are the values, where parametrization (with ? in sqlite3) is supported,
> the only vulnerable part; whereas eg. an incorrect value of what should
> be a name is safe (of course, apart from the unsuccessful command itself)?
>
Ultimately that depends where the table and column names come from. If
they are user inputs then you are still vulnerable to SQL injection, but
usually that's not the case when a query is being parameterized -
usually it's values.
As long as you consider the source of your data carefully you'll
probably be OK.