Re: update images inside a mysql database

[Jonas Meurer]

On 25/02/2005 Dennis Lee Bieber wrote:
 On Thu, 24 Feb 2005 23:10:48 +0100, Jonas Meurer [EMAIL PROTECTED]
 declaimed the following in comp.lang.python:
 
 
  version used placeholders as well. anyway, i changed my code to resemble
 
   resemble is the key... It is NOT the correct sample.
 
261 db_connect.cursor.execute(UPDATE Images SET Image=%s WHERE
262 ImgID = %s % (image, imgid))
  262 ImgID = %s , (image, imgid))
 
   The .execute method ITSELF performs the argument conversion into
 the place holders, including applying what ever quoting is required.
 YOUR version is using the Python formatting operator, which does NOT
 handle database quoting.

oh, your correct.

sorry for the noise.

now it works quite good.

bye
 jonas
-- 
http://mail.python.org/mailman/listinfo/python-list

update images inside a mysql database

[Jonas Meurer]

hello,

i develop a project with a mysql interface. one mysql table holds all
the images for my project.

everything works quite well so far, except i'm not able to upload images
into the database. the following function does the mysql UPDATE, it
requires the image and the image ID as arguments.

additionally it dumps the image to my filesystem ('/tmp/image.gif') -
i test this with gif images.

for some reason, the file dumped to /tmp/image.gif is exactly the same
as uploaded, but in mysql the file is corrupted, far to small and not
even viewable.

here is my function:

def i_update(image, imgid):
image = %s % (image)
sql_exec = UPDATE Images SET Image='%s' WHERE ImgID = '%s'
 % (image, imgid)
o = open(/tmp/file.jpg, w)
o.write(image)
o.close()
db_connect.cursor.execute(sql_exec)


i've the strong feeling that the substitution makes problems. if the
string 'image' conains ' or  for example, the sql_exec is broken.

what do you suggest?

bye
 jonas
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: update images inside a mysql database

[Gabriel Cooper]

Jonas Meurer wrote:
def i_update(image, imgid):
   image = %s % (image)
   sql_exec = UPDATE Images SET Image='%s' WHERE ImgID = '%s'
% (image, imgid)
   o = open(/tmp/file.jpg, w)
   o.write(image)
   o.close()
   db_connect.cursor.execute(sql_exec)
I've never tried extensively to use images inside a database (too slow 
for most of my uses), but I thought I'd drop in to point out that you 
should, for security reasons, be using place holders on your sql. It 
might just fix your image problem as well, but I don't know. Also, 
converting a binary image into a string doesn't seem like it would be 
wise, but like I said, I've never tried it. At any rate, your function 
would look like this:

def i_update(image, imgid):
   image = %s % (image)
   o = open(/tmp/file.jpg, w)
   o.write(image)
   o.close()
   db_connect.cursor.execute(UPDATE Images SET Image=%s WHERE ImgID=%s, 
  (image, imgid))

--
http://mail.python.org/mailman/listinfo/python-list

Re: update images inside a mysql database

[Jonas Meurer]

On 24/02/2005 Gabriel Cooper wrote:
 I've never tried extensively to use images inside a database (too slow 
 for most of my uses), but I thought I'd drop in to point out that you 
 should, for security reasons, be using place holders on your sql. It 
 might just fix your image problem as well, but I don't know.

i don't know what your example changes with using placeholders, as my
version used placeholders as well. anyway, i changed my code to resemble
your version. i still get the same problem:
(cgitb output)

---SNIP-
 /home/jonas/public_html/inventaria/mods/backend.py in 
i_update(image='\xff\xd8\xff\xe0\x00\x10JFIF\x00\x01\x01\x01\x00\x00\x00\x00\x00\x00\xff\xdb\x00C\x00\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01...#$\x82\x08f\xe5A\xc6\xe2G\xe4I\xa9\x18\x96.\xa7\x18T\x0e\x08\xe0\x92q\x9c\x9e\xa4s\xd3\xb7j(\xa0\n\xa4\x95\x00\x03\xdd\x87\xafC\
xefE\x14P\x07\xff\xd9', imgid='18')
  259 o.write(image)
  260 o.close()
  261 db_connect.cursor.execute(UPDATE Images SET Image=%s WHERE
  262 ImgID = %s % (image, imgid))
  263
global db_connect = module 'db_connect' from 'mods/db_connect.pyc',
db_connect.cursor = MySQLdb.cursors.Cursor object,
db_connect.cursor.execute = bound method Cursor.execute of 
MySQLdb.cursors.Cursor object, image = 
'\xff\xd8\xff\xe0\x00\x10JFIF\x00\x01\x01\x01\x00\x00\x00\x00\x00\x00\xff\xdb\x00C\x00\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01\x01...#$\x82\x08f\xe5A\xc6\xe2G\xe4I\xa9\x18\x96.\xa7\x18T\x0e\x08\xe0\x92q\x9c\x9e\xa4s\xd3\xb7j(\xa0\n\xa4\x95\x00\x03\xdd\x87\xafC\xefE\x14P\x07\xff\xd9',
 imgid = '18'

 /usr/lib/python2.3/site-packages/MySQLdb/cursors.py in 
execute(self=MySQLdb.cursors.Cursor object, query='UPDATE Images SET 
Image=\xff\xd8\xff\xe0\x00\x10JFIF\x00\x01\x01\x01\x00\x00\x00\x00\x00\x00\xff\xdb\x00C...\xe0\x92q\x9c\x9e\xa4s\xd3\xb7j(\xa0\
 n\xa4\x95\x00\x03\xdd\x87\xafC\xefE\x14P\x07\xff\xd9 WHERE\n\t\t\t\tImgID = 
18', args=None)
   93 
   94 del self.messages[:]
   95 return self._execute(query, args)
   96
   97 def _execute(self, query, args):
self = MySQLdb.cursors.Cursor object, self._execute = bound method 
Cursor._execute of MySQLdb.cursors.Cursor object, query = 'UPDATE Images SET 
Image=\xff\xd8\xff\xe0\x00\x10JFIF\x00\x01\x01\x01\x00\x00\x00\x00\x00\x00\xff\xdb\x00C...\xe0\x92q\
 
x9c\x9e\xa4s\xd3\xb7j(\xa0\n\xa4\x95\x00\x03\xdd\x87\xafC\xefE\x14P\x07\xff\xd9 
WHERE\n\t\t\t\tImgID = 18', args = None

[...]

 /usr/lib/python2.3/site-packages/MySQLdb/connections.py in 
defaulterrorhandler(connection=_mysql.connection open to 'localhost' at 
691500, cursor=MySQLdb.cursors.Cursor object, errorclass=class 
_mysql_exceptions.OperationalError, 
errorvalue=_mysql_exceptions.OperationalError instance)
   31 else:
   32 connection.messages.append(error)
   33 raise errorclass, errorvalue
   34
   35
errorclass = class _mysql_exceptions.OperationalError, errorvalue = 
_mysql_exceptions.OperationalError instance

 OperationalError: (1054, Unknown column 
'\xff\xd8\xff\xe0' in 'field list')
 args = (1054, Unknown column 
'\xff\xd8\xff\xe0' in 'field list')
---SNIP-
the problem is obviously, that the 'string' Image contains characters
that make it end ealier than expected.

 converting a binary image into a string doesn't seem like it would be 
 wise, but like I said, I've never tried it. At any rate, your function 
 would look like this:

i've been told on #python that unix doesn't differ between binary and
ascii, thus storing binary data should be no problem.

i've no glue about how to solve this problem. even if i use quotation
marks of any kind for the mysql values, what sometimes circumvents the
problem above, at best some some 1000 byte big blob is stored in the
mysql database, what is neither an image nor has the size of my uploaded
image.

any suggestions?

bye
 jonas
-- 
http://mail.python.org/mailman/listinfo/python-list