Literal Escaped Octets

[Steve Holden]

Chason Hayes wrote:
> On Tue, 07 Feb 2006 01:58:00 +0000, Steve Holden wrote:
> 
> 
>>Chason Hayes wrote:
>>
>>>On Mon, 06 Feb 2006 13:39:17 +0000, Steve Holden wrote:
>>
>>[...]
>>
>>>>The URL you reference is discussing how you represent arbitrary values 
>>>>in string literals. If you already have the data in a Python string the 
>>>>best advise is to use a parameterized query - that way your Python DB 
>>>>API module will do the escaping for you!
>>>>
>>>>regards
>>>> Steve
>>>
>>>
>>>Thanks for the input. I tried that with a format string and a
>>>dictionary, but I still received a database error indicating illegal
>>>string values. This error went away completely when I used a test file
>>>consisting only of text, but reproduced everytime with a true binary file.
>>>If you can let me know where I am wrong or show me a code snippet with a
>>>sql insert that contains a variable with raw binary data that works,
>>>I would greatly appreciate it.
>>>
>>
>>I tried and my experience was exactly the same, which made me think less 
>>of PostgreSQL.
>>
>>They don't seem to implement the SQL BLOB type properly, so it looks as 
>>though that rebarbative syntax with all the backslashes is necessary. Sorry.
>>
>>regards
>>  Steve
> 
> 
> with regards to escaping data parameters I have found that I have to
> specifically add quotes to my strings for them to be understood by
> pstgresql. For example
> 
> ifs=open("binarydatafile","r")
> binarydata=ifs.read()
> stringdata=base64.encodestring(binarydata)
> 
> #does not work
> cursor.execute("insert into binarytable values(%s)" % stringdata)
> 
> #need to do this first
> newstringdata = "'" + stringdata + "'"
> 
> then the select statment works.
> Is this expected behavior? Is there a better way of doing this?
> 
> thanks for any insight

Yes, parameterize your queries. I assume you are using psycopg or 
something similar to create the database connection (i.e. I something 
that expects the "%s" parameter style - there are other options, but we 
needn't discuss them here).

The magic incantation you seek is:

cursor.execute("insert into binarytable values(%s)", (stringdata, ))

Note that here there are TWO arguments to the .execute() method. The 
first is a parameterized SQL statement, and the second is a tuple of 
data items, one for each parameter mark in the SQL.

Using this technique all necessary quoting (and even data conversion 
with a good database module) is performed inside the database driver, 
meaning  (among other things) that your program is no longer vulnerable 
to the dreaded SQL injection errors.

This is the technique I was hoping would work with the bytea datatype, 
but alas it doesn't. ISTM that PostgreSQL needs a bit of work there, 
even though it is otherwise a very polished product.

regards
  Steve
-- 
Steve Holden       +44 150 684 7255  +1 800 494 3119
Holden Web LLC                     www.holdenweb.com
PyCon TX 2006                  www.python.org/pycon/