Can't Encode Pic

[Carsten Haese]

Victor Subervi wrote:
> Hi;
> I have the following code:
> 
> import cgitb; cgitb.enable()
> import cgi
> import MySQLdb
> from login import login
> user, passwd, db, host = login()
> db = MySQLdb.connect(host, user, passwd, db)
> cursor= db.cursor()
> form = cgi.FieldStorage()
> pic1 = form.getfirst('pic1')
> cursor.execute('update products set pic1=%s where ID=1;' % pic1)
> 
> which throws the following error:
>
> [snip UnicodeDecodeErrors and various blind guesses...]
>  
> Please advise.

The UnicodeDecodeErrors are a red herring. The real problem is that
you're using string interpolation (the %-operator) to pass the image
data to the database, so your code is force-feeding binary junk directly
into the query string. This results in an SQL query that's not
syntactically correct.

In order to pass values to the database safely, you need to use
parameter binding. In this case, that'll look something like this:

cursor.execute('update products set pic1=%s where ID=1',
(MySQLdb.Binary(pic1),))

[That comma between the two closing parentheses is not a typo. Do not
leave it out!]

Note that I'm not using string interpolation to shoehorn the picture
contents into the query string. I'm passing two arguments to
cursor.execute(). The first is the query template, with a %s placeholder
for your image contents. (Note that the query template is a plain ASCII
string, so all your Unicode-related problems will disappear in a puff of
magic.) The second argument is a tuple containing the actual parameters
to be filled into the query by the database engine. This query needs
only one parameter, so I'm making a 1-tuple containing the picture
contents, wrapped inside a MySQLdb.Binary object to tell the database
that this is a binary object.

Hope this helps,

--
Carsten Haese
http://informixdb.sourceforge.net