Re: problem uploading pictures o mysql database

--------------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:[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 = '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,"<br>\n"; # for debugging only
> $sth = $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`
> ="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/[email protected]
>
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--