escape special chars before loading into MySQL 
Author Message
 escape special chars before loading into MySQL

Does Perl have any similar funtion to addslashes() that PHP does?
What I want to do is to insert uploaded file into MySQL.

=========
open(F,$upload_name) || die "can not open $uploaded for reading;
read F,$content,$upload_size;
close(F);

$mysql_dbh->do("INSERT INTO $table (content,filename,filesize) VALUES
('$content', '$upload_name','$upload_size')");
==========

My $content may have some special charactors, e.g single quote ('), that
must be escaped before it can be accepted by MySQL.  Just wondering if
there is any funtion that can escape _all_ the characters considered
special by MySQL?

Thanks for any help,

Bing



Tue, 31 May 2005 21:42:38 GMT  
 escape special chars before loading into MySQL
: Does Perl have any similar funtion to addslashes() that PHP does?
: What I want to do is to insert uploaded file into MySQL.

: =========
: open(F,$upload_name) || die "can not open $uploaded for reading;
: read F,$content,$upload_size;
: close(F);

: $mysql_dbh->do("INSERT INTO $table (content,filename,filesize) VALUES
: ('$content', '$upload_name','$upload_size')");
: ==========

: My $content may have some special charactors, e.g single quote ('), that
: must be escaped before it can be accepted by MySQL.  Just wondering if
: there is any funtion that can escape _all_ the characters considered
: special by MySQL?

: Thanks for any help,

how about a function called "quote" which is documented in DBI

        perldoc DBI



Tue, 31 May 2005 22:34:40 GMT  
 escape special chars before loading into MySQL
Ah, thanks for the pointer.  I saw this in the manual:

=========
Quote will probably not be able to deal with all
           possible input (such as binary data or data containing
           newlines), and is not related in any way with escaping
           or quoting shell meta-characters. There is no need to
           quote values being used with the section on
           /"Placeholders and Bind Values.
=========

So it does not fit out needs because uploaded files can be of any type.  I'm
thinking about using MIME::Base64.  Anybody has any better suggestions?

Thanks,

Bing

Quote:


> : Does Perl have any similar funtion to addslashes() that PHP does?
> : What I want to do is to insert uploaded file into MySQL.

> : =========
> : open(F,$upload_name) || die "can not open $uploaded for reading;
> : read F,$content,$upload_size;
> : close(F);

> : $mysql_dbh->do("INSERT INTO $table (content,filename,filesize) VALUES
> : ('$content', '$upload_name','$upload_size')");
> : ==========

> : My $content may have some special charactors, e.g single quote ('), that
> : must be escaped before it can be accepted by MySQL.  Just wondering if
> : there is any funtion that can escape _all_ the characters considered
> : special by MySQL?

> : Thanks for any help,

> how about a function called "quote" which is documented in DBI

>         perldoc DBI



Tue, 31 May 2005 23:48:21 GMT  
 escape special chars before loading into MySQL
: Thanks,


: > : Does Perl have any similar funtion to addslashes() that PHP does?
: > : What I want to do is to insert uploaded file into MySQL.
: >
: > : =========
: > : open(F,$upload_name) || die "can not open $uploaded for reading;
: > : read F,$content,$upload_size;
: > : close(F);
: >
: > : $mysql_dbh->do("INSERT INTO $table (content,filename,filesize) VALUES
: > : ('$content', '$upload_name','$upload_size')");
: > : ==========
: >
: > : My $content may have some special charactors, e.g single quote ('), that
: > : must be escaped before it can be accepted by MySQL.  Just wondering if
: > : there is any funtion that can escape _all_ the characters considered
: > : special by MySQL?
: >
: > : Thanks for any help,
: >
: > how about a function called "quote" which is documented in DBI
: >
: >         perldoc DBI

: Ah, thanks for the pointer.  I saw this in the manual:

: =========
: Quote will probably not be able to deal with all
:            possible input (such as binary data or data containing
:            newlines), and is not related in any way with escaping
:            or quoting shell meta-characters. There is no need to
:            quote values being used with the section on
:            /"Placeholders and Bind Values.
: =========

: So it does not fit out needs because uploaded files can be of any type.  I'm

You said you wanted something to escape special characters, and that is
what quote does.  If quote can't quote the string into a format acceptable
by a mysql insert statement then no other quote/char-escape method will do
that either, including the php addslashes method you initially mentioned
(though mysql is pretty liberal about what data can be inserted by
including it literally in an insert if correctly quoted).

: thinking about using MIME::Base64.  

Well you can always (as you now mention) encode the data yourself if you
wish. That can always be done with any database.  On the other hand you
then have to decode the data every time you extract it, whereas if you
inserted the data itself (as opposed to an encoding of it) then you could
retrieve the data directly without decoding it later.

: .. Anybody has any better suggestions?

How about re-reading the last sentence of the paragraph from the manual
that you referenced above. (hint: "bind")

You also have to define the correct column datatype to receive the data.



Wed, 01 Jun 2005 00:44:46 GMT  
 escape special chars before loading into MySQL


Quote:
> Ah, thanks for the pointer.  I saw this in the manual:

> =========
> Quote will probably not be able to deal with all
>            possible input (such as binary data or data containing
>            newlines), and is not related in any way with escaping
>            or quoting shell meta-characters. There is no need to
>            quote values being used with the section on
>            /"Placeholders and Bind Values.
> =========

> So it does not fit out needs because uploaded files can be of any type.  I'm
> thinking about using MIME::Base64.  Anybody has any better suggestions?

use "Placeholders and Bind Values" like it says in that bit of the
manual. That's what I always do.

P

--
pkent 77 at yahoo dot, er... what's the last bit, oh yes, com
Remove the tea to reply



Wed, 01 Jun 2005 02:15:54 GMT  
 escape special chars before loading into MySQL
[snip]

Quote:
> $mysql_dbh->do("INSERT INTO $table (content,filename,filesize) VALUES
> ('$content', '$upload_name','$upload_size')");

   $mysql_dbh->do( qq[
      INSERT INTO $table
      (content, filename, filesize) VALUES (?, ?, ?)
   ], undef, $content, $upload_name, $upload_size );

--
$..='(?:(?{local$^C=$^C|'.(1<<$_).'})|)'for+a..4;
$..='(?{print+substr"\n !,$^C,1 if $^C<26})(?!)';
$.=~s'!'haktrsreltanPJ,r  coeueh"';BEGIN{${"\cH"}
|=(1<<21)}""=~$.;qw(Just another Perl hacker,\n);



Thu, 02 Jun 2005 06:22:50 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Replacing special chars with special chars

2. is there a way to escape special chars

3. Newbie - Escaping special chars in forms

4. DBD::mysql not loading mysql.dll

5. regex to escape special characters

6. Shell special chars in file names?

7. pattern matching with special char

8. interpolate escaped chars in string

9. Adding escape chars to email address

10. Special chars

11. special chars

12. about special chars when split()

 

 
Powered by phpBB® Forum Software