storing binary files to mySQL ???

[Gerhard Häring]

* Fredrik Lundh <fredrik at pythonware.com> [2002-12-16 19:58 +0000]:
> "steindl fritz" <python at floSoft.org> wrote:
> 
> > i try to store pictures in a blob field of mySQL
> >
> >
> >     file = open("c:\\picture.jpg", 'rb')
> >     picture = file.read()
> >
> >     SQL ='INSERT INTO tbl_BLOB (attrName, attrValue) VALUES
> > ("nameOfPicture", ' + picture + ')'
> >
> >     conn = CompatMysqldb.Connection("host", "username", "password", "db")
> >     curs = conn.cursor()
> >     curs.execute(SQL)

DONT'T. EVER. DO. THAT. (constructing INSERT/UPDATE/DELETE statements
manually). It's a bad habit that will bite you once. In your case, it
did already. I've seen security flaws introduced this way and data
accidentally deleted. If you don't see how this can happen, I'm happy to
show you a few examples.

> what MySQL module are you using?
> 
> ("python CompatMysql" (without the quotes) is a googlewhack
> on groups.google.com, and returns no hits at all on other parts
> of google.)

Speaking of Google, we had this question once already: 

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=slrnad7doc.1b6.gerhard%40lilith.my-fqdn.de&rnum=1&prev=/groups%3Fq%3Dpython%2Bmysqldb%2Bblob%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3Dslrnad7doc.1b6.gerhard%2540lilith.my-fqdn.de%26rnum%3D1

    or

http://tinyurl.com/3ln6

> anyway, in case this is Andy Dustman's version, and nothing else
> helps, you can always import _mysql and use the escape_string
> function:
> 
>     import _mysql
> 
>     file = open("c:\\picture.jpg", 'rb')
>     picture = _mysql.escape_string(file.read())

There's no need to use escape_string, or _mysql for that matter.

> I'm pretty sure there's a better way, 

The better way is to let the DB-API 2.0 module handle the quoting for
you:

#v+
import MySQLdb

con = MySQLdb.connect(host="myhost", user="myuser", passwd="sicritunderground", db="xyz")

# Let's construct some binary data
bindata = "".join([chr(x) for x in range(256)] * 3)

# A string
s = "foo"

# Schema: create table test (t text, b blob)
cursor = con.cursor()

cursor.execute("insert into test(t, b) values (%s, %s)", (s, bindata))

cursor.execute("select b from test where t=%s", (s,))

row = cursor.fetchone()
retrieved_bindata = row[0]

# Check wether we got the same binary data we inserted.
assert retrieved_bindata == bindata

con.close()
#v-

> but I just uninstalled MySQL on this box, so I'll have to leave it
> to someone else to tell you what it is.

Gerhard
-- 
Favourite database:             http://www.postgresql.org/
Favourite programming language: http://www.python.org/
Combine the two:                http://pypgsql.sf.net/