problem uploading pictures o mysql database

am 14.05.2010 13:02:06 von Sebastian Reinhardt

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 = 'UPDATE `offers`.`preowned` SET `pic0` =qw(';
while(read $picture,$data,1024)
{
$sql_stmnt .= $data;
}
$sql_stmnt .= ') WHERE `preowned`.`Ref_nr` ='.$refnr;
print "sql_stmnt:",$sql_stmnt,"
\n"; # for debugging only
$sth = $dbh->prepare($sql_stmnt);
print "sql_status:",$sth->err(),":",$sth->errstr()," 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`
="GIF89aï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½!ï¿½Created with GIMPï¿½,ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½;" WHERE
`preowned`.`Ref_nr` =2147483647
sql_status::

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

Output for same file as jpg:
----------------------------------------------
pic_size:1
pic_size:7
pic_size:JPG
sql_stmnt:UPDATE `offers`.`preowned` SET `pic0`
="ï¿½ï¿½ï¿½ï¿½ï¿½JFIFï¿½ï¿½Hï¿½Hï¿½ï¿½ï¿½ï¿½ï¿½Created with GIMPï¿½ï¿½ï¿½Cï¿½ ï¿½ï¿½ï¿½C ï¿½ï¿½ï¿½ï¿½ï¿½"ï¿ ½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿ ½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï ¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï ¿½ï¿½
ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ ï¿½ï¿½ï¿½ï¿½ï¿½?ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½" WHERE `preowned`.`Ref_nr`
=2147483647
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/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

RE: problem uploading pictures o mysql database

am 15.05.2010 04:14:48 von Matthew Braid

------_=_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:snr@lmv-hartmannsdorf.de]
Sent: Fri 14-May-10 21:02
To: perl@lists.mysql.com
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,"
\n"; # for debugging only
$sth =3D $dbh->prepare($sql_stmnt);
print "sql_status:",$sth->err(),":",$sth->errstr()," 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????;" 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?H?H?????=13Created with =
GIMP???C?=01 =01=
=01=
=01???C=01=0 1=
=01=
=01=
=01????=07?=01"?=0 1=01???=
=15????????????????=08???=01???????????????????= 14?????=
??????????
???=01??????????????????? ??????=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/perl?unsub=3Dmdb@auscert.org.au

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

Re: problem uploading pictures o mysql database

am 15.05.2010 13:31:15 von Sebastian Reinhardt

--------------070501000505010009030005
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Am 15.05.2010 04:14, schrieb Matthew Braid:
> Hi,
>
> 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).
>
> If you have arbitrary data to be put into an SQL statement, NEVER just
> dump it into the SQL whole.
>
> 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:
>
> my $sql = 'UPDATE offers.preowned SET pic0 = ? WHERE preowned.Refnr = ?';
> my $sth = $dbh->prepare($sql);
> # Check errors
> my ($picdata, $refn) = get_picdata_and_ref_number(); # expand as necessary
> $sth->execute($picdata, $refn); # This is automatically made safe for you
> # Check errors
>
> 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:
>
> my $safe_data = $dbh->quote($arbitrary_data);
>
> but that's slower and more typing.
>
> 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.
>
> MB
> ------------------------------------------------------------ ------------
> *From:* Sebastian Reinhardt [mailto:snr@lmv-hartmannsdorf.de]
> *Sent:* Fri 14-May-10 21:02
> *To:* perl@lists.mysql.com
> *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 = 'UPDATE `offers`.`preowned` SET `pic0` =qw(';
> while(read $picture,$data,1024)
> {
> $sql_stmnt .= $data;
> }
> $sql_stmnt .= ') WHERE `preowned`.`Ref_nr` ='.$refnr;
> print "sql_stmnt:",$sql_stmnt,"
\n"; # for debugging only
> $sth = $dbh->prepare($sql_stmnt);
> print "sql_status:",$sth->err(),":",$sth->errstr()," > 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`
> ="GIF89a?????????!?Created with GIMP?,?????????;" WHERE
> `preowned`.`Ref_nr` =2147483647
> sql_status::
>
> ----------------------------------------------
>
> Output for same file as jpg:
> ----------------------------------------------
> pic_size:1
> pic_size:7
> pic_size:JPG
> sql_stmnt:UPDATE `offers`.`preowned` SET `pic0`
> ="?????JFIF??H?H?????Created with GIMP???C? ???C ?????"????????????????? ????????????????????????????????????????
> ?????????????????????? ??????????" WHERE `preowned`.`Ref_nr`
> =2147483647
> 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/perl?unsub=mdb@auscert.org.au
>
Thanks for Your reply. I have customized the code a little bit and it
works. The next step (I have planned) is reworking my script , also
regaarding the SQL- injection problem.

--
Kind regaards

Sebastian Reinhardt

--------------070501000505010009030005--