Re: [Pypgsql-users] Parameters etc.

[Gerhard <ger...@gm...>]

* Dick Kniep <d.j...@ch...> [2002-10-03 15:39 +0200]:
> Hi List,
> 
> Is it possible to write a simple "Insert into "tablenm" (column1,
> column2) Values (value1, value2)" instead of "Insert into "tablenm"
> (column1, column2) Values (%s, %s)" and give the values as a tuple?

Dick,

I don't think I understand what you're trying to accomplish.

The normal way to insert values using pyPgSQL is

    cursor.execute("insert into t(c1, c2) values (%s, %s)", (1, 2))

and I'd really recommend you use this DB-API style.

If I understand correctly, you don't quite like this and want to do it
somehow different?

Of course you can use something like:

    cursor.execute("insert into t(c1, c2) values (1, 2)")

and omit the optional second parameter of the execute (and executemany
and callproc) methods, if the values you're trying to insert _constant_.


But please note that using something like:

    cursor.execute("insert into t(c1, c2) values (%s, %s)" % (1, 2))

does work for ints but you'll certainly get into trouble for other
types. IOW, the DB-API way of quoting for SQL is highly recommended, as
it will take care of proper quoting _for SQL_ (think of single
apostrophs in a Python string, for example):

    cursor.execute("insert into t(s) values (%s)" % "Fred's house")

    ^
    +----- This will _not_ work and yield an error. Using DB-API style,
           this is would have been no problem at all.

    cursor.execute("insert into t(s) values (%s)", ("Fred's house"))

One more reason for using DB-API style is security. Consider this:

    val = {something that comes from an untrusted source, for example a
           CGI script}

    # Now let val be something that a user entered in a web form. If the
    # user was me, it will look like for an "age" field:
    # 5) * 1(; DELETE FROM AGE;

    cursor.execute("insert into t(age) values (%s)" % val)

    This will expand to:

    insert into t(age) values (5) * (1); DELETE FROM AGE;

    Again, with DB-API style quoting this could not have happened, as it
    would have been expanded to:

    insert into t(age) values ('5) * 1(; DELETE FROM AGE;')

    and would have yielded an error from the backend, as it tries to
    insert a string into an int field.

-- Gerhard