Adding Images To MySQL

[Gabriel Genellina]

En Sat, 05 Apr 2008 11:32:00 -0300, Victor Subervi  
<victorsubervi at gmail.com> escribió:

>> * *- You say Content-Type: image/jpeg but you emit HTML code. You're  
>> lucky
> if you see any
>
>> * *text at all.
>
> Well, I tried Content-Type: text/html and that threw an HTTP 500 Error.

If your script raised any exception, that's the expected code. (500 =  
internal error = your code has errors). But it's not very useful for  
debugging; using cgitb or wrapping the code in try/except as has already  
been suggested, lets you see the exception and traceback.

>> * *- HTTP 200 is not an error, it means the request was successful.
>
> When it doesn´t execute the code, how can it be called successful? If it
> doesn´t execute the code, how can you say it´s not an error? What is it,
> then?

Well, your web server thinks all went ok... BTW, you did't provide details  
about it, I *guess* you're using CGI because of the print "Content-Type"  
line.

>> * *- As a general advice, try to isolate the problems. Test the database
> stuff alone, in a local
>> * *application. Test the cgi script alone, without database interaction.
> Test the database stuff in
>> * *the web server (better if you have a shell account). Merge all and  
>> test
> again.
>
> Very good advice. Please help me understand how to do that.
>
> This is what I have done. I have tried these:
>
> sql = "'insert into products (" + col_names + ") values (" + val + ")',  
> (" +
> col_names + ")"
>
> cursor.execute(sql)
>
> and
>
> sql = "'insert into products (" + col_names + ") values (" + val + ")'"
>
> cursor.execute(sql, (col_names,))
>
> Neither work.

You got a syntax error, I guess. What's that ' at the start?
I'll try to explain it from the ground up. This would be a valid SQL  
statement::

     insert into PRODUCTS (PRODID, NAME, DESCRIPTION)
     values (123, 'Easter egg 80g', 'A longer description');

In Python, you need the SQL text inside a string::

     sql = "insert into PRODUCTS (PRODID, NAME, DESCRIPTION) " \
           "values (123, 'Easter egg 80g', 'A longer description');"

and you can execute it with::

     cursor.execute(sql)

That would be OK when you create the database for the first time. Later  
your boss comes in and says: "We've got bigger eggs! Code 124, 150g each.  
We need them in the database". You write an sql statement similar to  
above. Some days later, they decide to sell bubble gum. Forty-two  
different sizes and flavors and brands. You don't want to write all that  
sql statements by hand. Your first thought is to build the sql text by  
pieces::

     sql = "insert into PRODUCTS (PRODID, NAME, DESCRIPTION) " \
           "values ("+str(prodid)+", '"+prodname+"', '"+description+"');"

But then you remember to have read something about sql injection and you  
don't like that mess of " + ) ' ( anyway. After reading some articles  
about DBAPI 2, PEP 249, the MySQLdb module, you write this::

     sql = "insert into PRODUCTS (PRODID, NAME, DESCRIPTION) " \
           "values (%s,%s,%s);"
     cursor.execute(sql, (prodid, prodname, description))

and it works fine.

Note that execute has two arguments: first, a string with the sql  
statement text; second, a tuple containing the values. The %s in the sql  
text are placeholders, they're replaced with the corresponding value from  
the second argument. And there is no ' anywhere.

> However, if I print what that code spits out:
>
> sql = "'insert into products (" + col_names + ") values (" + val + ")',  
> (" +
> col_names + ")"
>
> print sql
>
> then copy and paste it into a cursor.execute() statement, viola!  
> Everything
> works _just_fine_. Go figure. Why??

What you have done has no sense - why do you think it should work?

py> a = "2 " + ", " + "3"
py> pow(a)
Traceback (most recent call last):
   File "<stdin>", line 1, in <module>
TypeError: pow expected at least 2 arguments, got 1
py> print a
2 , 3
py> pow(2 , 3)
8

Your first attempt used a long string with embedded quotes, it is a  
*single* argument to execute. If you print it and interpret the result as  
Python code, there are two items: now you are passing *two* arguments to  
execute.

> pic1 = _mysql.escape_string(f)
>
> It does not like this (forgot error):
>
> pic1 = MySQLdb.Binary(f)

You should make the above work. Look at the exception, read the error  
message, look at the traceback. There are a lot of info there.

> Escaping the string, I can successfully load this image into the  
> database,
> along with all the other fields. Now, when I load an image from the form  
> on
> the previous page with this code:
>
> <input type='file' name='pic1' value="""
>
> print '"', MySQLdb.Binary(data[14]), '"'
>
> and send the form off to the next page, when I process it on that page

Why do you do *that*??? That <input> element is for the user to upload a  
file; if the file is already on the server, why do you transfer it from  
server to client and back to server? I doubt it can work this way. And why  
are you using Binary here? Binary is for sql stuff and you're building an  
HTML page here. (btw, didn't you say that Binary doesn't work?).

> pic1 = _mysql.escape_string(pic1)
>
> print pic1
>
> it prints out a messy binary that (almost) starts with something like  
> ¨This
> program can only be run in Win32¨, whatever that means. But a binary is
> printed. (It may be an infinite binary, I stopped it after a few  
> minutes.)

Looks like a .exe; somehow you managed to upload a .exe instead of a jpg,  
I presume...

> Now, if I stick it into the cursor.execute like I did above, it throws an
> HTTP non-error non-posting-to-the-database 200 error. I´m more than  
> happy to
> separate all this garbage out for further testing, but I don´t know how  
> to
> do that. Your help is very much appreciated.

It seems you have a basic misunderstanding of how web applications work.  
Reading a book like "Python Web Programming" by Steve Holden might help.
http://www.amazon.com/Python-Programming-Landmark-Steve-Holden/dp/0735710902

-- 
Gabriel Genellina