RE: problem uploading pictures o mysql database

------_=_NextPart_001_01CAF3D4.65143897
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi,
=20
Not sure if this will make it to the list, but this is the basic =
background problem that causes SQL injection vuls in websites (or =
anything that builds SQL statements).
=20
If you have arbitrary data to be put into an SQL statement, NEVER just =
dump it into the SQL whole.
=20
What you want is to use placeholders in your perl code. I haven't =
written code that needs to reference database and statement handles raw =
for a while, but it's something like:
=20
my $sql =3D 'UPDATE offers.preowned SET pic0 =3D ? WHERE preowned.Refnr =
=3D ?';
my $sth =3D $dbh->prepare($sql);
# Check errors
my ($picdata, $refn) =3D get_picdata_and_ref_number(); # expand as =
necessary
$sth->execute($picdata, $refn); # This is automatically made safe for =
you
# Check errors
=20
You could also not use the ? holder if you really didn't want to, but =
you'd have to run your data through the 'quote' function:
=20
my $safe_data =3D $dbh->quote($arbitrary_data);
=20
but that's slower and more typing.
=20
If this is how you've been building SQL statements up until now, I'd =
recommend reading up on SQL injection and going over your existing code.
=20
MB

________________________________

From: Sebastian Reinhardt [mailto:[email protected]]
Sent: Fri 14-May-10 21:02
To: [email protected]
Subject: problem uploading pictures o mysql database



Hello,

In one of my perl cgi scipts, I try to realize an upload to store some
pictures and description in an mysql database.
The text upload is working, but with some picture data I get trouble.
Here is the code snippet, which should upload the picture data:
-------------------------------------------------------------------------=
------------------------
my $sql_stmnt =3D 'UPDATE `offers`.`preowned` SET `pic0` =3Dqw(';
while(read $picture,$data,1024)
      {
              $sql_stmnt .=3D $data;
      }
$sql_stmnt .=3D ') WHERE `preowned`.`Ref_nr` =3D'.$refnr;
print "sql_stmnt:",$sql_stmnt,"<br>\n"; # for debugging only
$sth =3D $dbh->prepare($sql_stmnt);
print "sql_status:",$sth->err(),":",$sth->errstr(),"<br\n"; # for
debugging only
$sth->execute();
-------------------------------------------------------------------------=
------------------------
So I have an sample picture as *.gif an d*.jpg. Uploading the gif is no
problem (only for this special gif- file!), but uploading the same
picutre as jpg is not possible. This is because in jpg- code some ' and
" signs are included! So how can I tell mysql, that these are binary
and  no mysql- related signs? I tried also an "BIN()" arounbd the
picture data, but without success.

Output for gif- upload:
----------------------------------------------
pic_size:1
pic_size:7
pic_size:GIF
sql_stmnt:UPDATE `offers`.`preowned` SET `pic0`
=3D"GIF89a=01?=07??=01???=01???!?=11Created with =
GIMP?,????=01?=07??=02=03?=0F=05?;" WHERE
`preowned`.`Ref_nr` =3D2147483647
sql_status::

----------------------------------------------

Output for same file as jpg:
----------------------------------------------
pic_size:1
pic_size:7
pic_size:JPG
sql_stmnt:UPDATE `offers`.`preowned` SET `pic0`
=3D"?????=10JFIF?=01=01=01?H?H?????=13Created with =
GIMP???C?=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=
=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=
=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01???C=01=01=01=01=01=01=
=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=
=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=01=
=01=01=01=01=01=01=01=01=01???=11=08?=07?=01=03=01"?=02=11=01=03=11=01???=
=15?=01=01???????????????=08???=14=10=01???????????????????=14=01=01?????=
??????????
???=14=11=01??????????????????? =03=01?=02=11=03=11?????=15?=7F??" WHERE =
`preowned`.`Ref_nr`
=3D2147483647
sql_status:1064:You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near '' at line 1
----------------------------------------------

Every entry is referenced by "Ref_nr". This number is an timestamp and
so every entry can identified by this number.

--
Kind regaards

Sebastian Reinhardt



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    =
http://lists.mysql.com/[email protected]




------_=_NextPart_001_01CAF3D4.65143897--