pypgsql-users

[Pypgsql-users] Prepared Statements

[Ben L. <be...@zu...>]

Hi,

In my program I was building SQL queries in this fashion:

sql = "SELECT wibble FROM wobble "
sql += " WHERE somefield = '%s';" % some_variable
cursor.execute(sql)

Doing it properely gives me flexibility, especially with regard to quoting 
bytea values, and security, viz:

cursor.execute("SELECT wibble FROM wobble WHERE somefield = %s;", 
some_variable)

However, some of my SQL statements get rather complicated, is there a way of 
building one up gradually then passing it to cursor.execute()? Sometimes I 
need to include/exclude entire subclauses.

Thanks,

Ben Lamb.

Re: [Pypgsql-users] Prepared Statements

[Karsten H. <Kar...@gm...>]

Ben,

you mean like

sql = "SELECT wibble FROM wobble "

if foo:
    sql += " WHERE somefield = '%s';" % some_variable
elif bar:
    sql += " WHERE someotherfield = '%s';" % some_other_variable

?

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: [Pypgsql-users] Prepared Statements

[Karsten H. <Kar...@gm...>]

Hi all,

am I correct to assume that

 notification.relname

 (where notification is a PgNotify object returned
 by PgConnection.notifies())

gives me the name of the backend notification that was used
in "LISTEN <signal_name>;" ?  "relname" seems a bit misleading
here as it can be an arbitrary string of type "name" AFAICT.
It is certainly customary to use the table name but in GnuMed
we try to be more specific and also include the patient
primary key (if appropriate) in the signal name.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: [Pypgsql-users] Prepared Statements

[Billy G. A. <bil...@mu...>]

Karsten Hilbert wrote:

>Hi all,
>
>am I correct to assume that
>
> notification.relname
>
> (where notification is a PgNotify object returned
> by PgConnection.notifies())
>
>gives me the name of the backend notification that was used
>in "LISTEN <signal_name>;" ?  "relname" seems a bit misleading
>here as it can be an arbitrary string of type "name" AFAICT.
>It is certainly customary to use the table name but in GnuMed
>we try to be more specific and also include the patient
>primary key (if appropriate) in the signal name.
>
>Karsten
>  
>
Perhaps it is a little mis-leading,  but it matches the PostgreSQL 
documentation for libpq
function that the PgNotify object wraps, and the name of the element in 
the underlying
pgNotify (C) structure.

-- 
___________________________________________________________________________
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |

Re: [Pypgsql-users] signal name == relname, was: Prepared Statements

[Karsten H. <Kar...@gm...>]

> Perhaps it is a little mis-leading
Which seems to mean "yes" to my question.

Thanks,
Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: [Pypgsql-users] Prepared Statements

[Ben L. <pyp...@zu...>]

Exactly.

I'm writing an app with a web-based interface and I've made myself a load of 
objects that all inherit from a base class called Field. I can call a method 
and the Field will return HTML code to edit itself, if it is a DateField that 
will consist of some listboxes, for booleans a checkbox.

I then have some business objects which carry a list of fields. When I tell 
the business object to write itself to the database it constructs a query 
along of the lines of:

sql = "UPDATE %s SET " % bizobj.table
for field in bizobj.fields:
	sql += "%s = '%s' " % (field.name, field.value)
sql += ";"

When I accept input from the web I don't want someone attempting to inject 
their own SQL, nor do I want the query to fail if they type a character that 
needs escaping e.g. '

I could write a function to check all this myself but I'd rather use what's in 
pyPgSQL.

ByteA fields are causing me problems using the approach above, I get the 
following error when field.value is contains PgSQL.PgBytea( binary_data):

TypeError: query() argument 1 must be string without null bytes, not str

I'm not sure how to solve it.

Thanks,

Ben.



On Monday 14 Apr 2003 12:22 pm, Karsten Hilbert wrote:
> Ben,
>
> you mean like
>
> sql = "SELECT wibble FROM wobble "
>
> if foo:
>     sql += " WHERE somefield = '%s';" % some_variable
> elif bar:
>     sql += " WHERE someotherfield = '%s';" % some_other_variable
>
> ?
>
> Karsten

Re: [Pypgsql-users] Prepared Statements

[Gerhard H. <gh...@gh...>]

Ben Lamb wrote:
> Exactly.
> 
> I'm writing an app with a web-based interface and I've made myself a load of 
> objects that all inherit from a base class called Field. I can call a method 
> and the Field will return HTML code to edit itself, if it is a DateField that 
> will consist of some listboxes, for booleans a checkbox.
> 
> I then have some business objects which carry a list of fields. When I tell 
> the business object to write itself to the database it constructs a query 
> along of the lines of:
> 
> sql = "UPDATE %s SET " % bizobj.table
> for field in bizobj.fields:
> 	sql += "%s = '%s' " % (field.name, field.value)
> sql += ";"
> 
> When I accept input from the web I don't want someone attempting to inject 
> their own SQL, nor do I want the query to fail if they type a character that 
> needs escaping e.g. 
> 
> I could write a function to check all this myself but I'd rather use what's in 
> pyPgSQL. [...]

It works if you separate these two concerns:

1) Building the SQL string with placeholders
2) Telling the DB-API module which query to execute, and with which 
parameters

The trick with using Python's quoting is that if you double the percent 
sign, it is kept after quoting with the % operator. I confess that the 
mass of % signs below might be confusing at first sight ;-)

Attached is a working example of dynamically constructing a SQL string, 
while still using pyPgSQL's quoting of the various types. Note that 
pyPgSQL can handle a dictionary as the second parameter to cursor.execute.

-- Gerhard

Re: [Pypgsql-users] Prepared Statements

[Karsten H. <Kar...@gm...>]

> sql = "UPDATE %s SET " % bizobj.table
> for field in bizobj.fields:
> 	sql += "%s = '%s' " % (field.name, field.value)
> sql += ";"
I always had to use

sql = "update table set field=%s;"
cursor.execute(sql, PgBytea(value))

with bytea data. It didn't work the other way.

Note that it is permissible to have more formatters in a
string than there are values to replace them. Just be careful
to replace the right ones at the right time.

> TypeError: query() argument 1 must be string without null bytes, not str
'%s' % value will in effect insert str(value) which isn't
right for ByteA.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: [Pypgsql-users] Prepared Statements

[Ben L. <pyp...@zu...>]

Thank-very much for your help Gerhard and Karsten. I've just implemented the 
code suggested by Gerhard and it does exactly what I require.

It can see it should work with the Bytea stuff but I need to make some more 
changes to by app before I can test that.

Thanks again,

Ben.

P.S. If anyone is writing web apps I can highly recommend the combination 
Postgres, pyPgSQL and mod_python. It runs extremely quickly on four year old 
hardware.


On Monday 14 Apr 2003 2:42 pm, Karsten Hilbert wrote:
> > sql = "UPDATE %s SET " % bizobj.table
> > for field in bizobj.fields:
> > 	sql += "%s = '%s' " % (field.name, field.value)
> > sql += ";"
>
> I always had to use
>
> sql = "update table set field=%s;"
> cursor.execute(sql, PgBytea(value))
>
> with bytea data. It didn't work the other way.
>
> Note that it is permissible to have more formatters in a
> string than there are values to replace them. Just be careful
> to replace the right ones at the right time.
>
> > TypeError: query() argument 1 must be string without null bytes, not str
>
> '%s' % value will in effect insert str(value) which isn't
> right for ByteA.
>
> Karsten