I'm writing a perl/mysql script for work and I've done this plenty of times in php, but the servers at work don't have php available. I've got most of the code down with no problems. I have one concern though. I'm just wanting to submit data, two fields for now, into a mysql db. $question and $answer.

Do I have to escape characters to do like

INSERT INTO $table ($fields) VALUES ($values)

I know in php I always escaped stuff like ' , and " but it has a handy little function called addslashes I'm afraid I'm not that good with regex in perl to do a eregi type replace for those characters. Anyone know of a good resource about reading up on this? Or know if an equivalent function in perl, or better yet, if it's even necessary?

Any info/help is MUCH appreciated! Thanks way up in advance!


-----------
Crowe (crowe@lit.org)

I just came up with a quick fix......don't know if it is what you are looking for?......

s/('|")/\\$1/g;

This will look for " and ' and replace them with \' and \"

If you want to escape other characters too just add them as follows.....

s/('|"|;)/\\$1/g; # Will escape ; too


Someone else will have to tell you if it is needed or not .





Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

I found another way, Your way is great btw, I'm adding that to my snippet collection ;)

The other way is because I'm using the DBI module.

$dbh->quote($foo)

Fixes it up nicely. A friend pointed this out to me. Something to keep note of as well!

Thank for the help, and the snappy reply!


-----------
Crowe (crowe@lit.org)

Well we both learned something

I didn't know about.....

$dbh->quote($foo)



Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

Using placeholders with DBI/DBD::* is nice too:

That will escape $value and put quotes around it before actually running the query. It certainly has its uses!

Jason Rhinelander
Gossamer Threads
jason@gossamer-threads.com

Just a quick update on this subject.

using the dbi quote method doesn't seem to work. No matter what kind of input I enter, it will not insert it into the db, I've tried the place holder method and it errors as well.

I realize that using the method that Paul Wilson was kind enough to give me isn't as portable as most coders like, but it works like a champ. I don't need this app to be too terribly portable just yet, I'm not at that level any how ;)

I'm just a hack trying to make a go of it! heh.

Thanks again for the help!

--Chris


-----------
Crowe (crowe@lit.org)

Careful though as imagine if someone passes in:

\'; DELETE FROM Table;

as input. This gets changed and you get:

\\'; DELETE FROM Table

which could cause problems (actually MySQL doesn't allow two queries at once, but other dbs do). Using $dbh->quote or placeholders will fix this and prevent this possible security hole.

Cheers,

Alex

--
Gossamer Threads Inc.

The only problem is, the only thing I could get to work is the regex .. Any time I added the dbh->quote it would not add to the mysql table.

I tried quoting before the query and no luck. I tried changing it to use place holders and got an error saying "do" not defined n such. I realize is probably because I'm a novice and just did something wrong ;) But I'm learning. I spent about an hour tonight reading the regex book!

I'll post the script tomorrow for people to see what I'm doing. (Don't have it here, on my work machine) ..


-----------
Crowe (crowe@lit.org)

So could you not just strip any backslashes from the input before you escape?



Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

Okay, here is the code I'm using for you guys to take a peep at. I stripped it down to the minimum so the other junk doesn't get in the way. This is the core of what I'm trying to do and where the problem lies is this:

I need to submit text mixed with html and punctuation. I want to allow html for formatting of paragraphcs with bold headings, lists etc. I can always impliment some hack of the UBB codes if necessary for the html. Right now the problem is the escaping the dangerous characters for sql. The code I have works but as you pointed out Alex, it's open to trouble. Also, it's not portable. If we switch to postgres or something else I may have to change my regex to match. with dbh->quote That would not be the case.

Here is the code:

<blockquote>
#!/usr/bin/perl
# --------------------------------------------------------
#

use CGI;
use DBI;
use Mysql;
use CGI::Carp qw(fatalsToBrowser);

$in=new CGI;

$dbh = Mysql->Connect ("$host","$db","$user","$pass") || die "cannot connect to database: $!\n";


$answer=$in->param('answer');
#$answer=$dbh->quote($answer);
$answer =~ s/('|")/\\$1/g;

$query = "INSERT INTO faqs (answer) VALUES('$answer');";
$sth = $dbh->Query($query) or error("cannot perform query: $query.");


print "Content-Type: text/html\n\n";
print "<h1>Insert FAQ Routine</h1>

\n";
print "Debug: $query";

</blockquote>

Any ideas what I'm doing wrong?

-----------
Crowe (crowe@lit.org)

get rid of
use Mysql;
and just use DBI.
you should be creating your database handle through DBI, thus giving it the DBI methods like quote().

-g


s/(\d{2})/chr($1)/ge + print if $_ = '8284703280698276687967';

Is this line not causing an error for you?

$query = "INSERT INTO faqs (answer) VALUES('$answer');";

I would have thought that the ; at the end of the mysql query would conflict the the ; that terminates the string.

use diagnostics; is meant to be good for getting a detailed error explanation. I use Carp a lot but my perlbook recommended use diagnostics although i've not tried it thoroughly yet..



Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

No, It hasn't. It was how I saw it done on severl "how to" pages.

The problem is I'm combining several how to pages ;)

Anyone know where a good how to on using just DBI is, the cpan readme just doesn't cut the mustard for me ..heh.


-----------
Crowe (crowe@lit.org)

No sure how useful this will be but may be worth a look....

http://www.ig.co.uk/perl/DBI_Talk3_1999/
http://dc.pm.org/perl_db.html
http://theoryx5.uwinnipeg.ca/CPAN/data/DBI/DBI.html

Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

Just found an AWESOME mysql dbi resource at the (kicking self) mysql web site:

http://www.mysql.com/doc/P/e/Perl_DBI_Class.html

Very much worth a look. I think this will get me done up right!

I'll post my results here in a few hours ;)

-----------
Crowe (crowe@lit.org)

That's the ticket! Here is my revised code using the DBI module instead of Mysql.

--CUT--

#!/usr/bin/perl
use CGI;
use DBI;
use CGI::Carp qw(fatalsToBrowser);

$in=new CGI;

$dbh = DBI->connect("DBI:mysql:$database:$hostname",$user, $password) || die "Could Not Establish Connection: $DBI::errstr";

$answer=$in->param('answer');
$answer=$dbh->quote($answer);
$query = "INSERT INTO faqs (answer) VALUES('$answer');";

$rv = $dbh->do($query) || die "Can't execute $statement: $dbh- >errstr\n";




print "Content-Type: text/html\n\n";
print "<h1>Insert FAQ Routine</h1>

\n";
print "Debug: $query";

$rc = $dbh->disconnect;

--CUT--

Worked fantstically.

Now, one other issue to resolve! These faq's include html in some of the instructions, showing users how to use certain html tags and so forth. So I need to escape this html, which is easy using:

use CGI qw/escapeHTML unescapeHTML/;
$hash{answer} = escapeHTML($hash{answer});

But, I want to let formatting tags through, like b,i,h1 etc.

Looking at the docs for the CGI.pm module, I don't think there is a way to make rules for certian tags, as it simply changes < to < and so on. I think the only solutions is to create a UBB style code for my faq system like [BOLD]bold[/BOLD] .. And regex replace those puppies. Any suggestions?


-----------
Crowe (crowe@lit.org)

You can use

use CGI qw(:standard);

....instead of

use CGI;

I'm not sure how much difference it will make but it's gotta be quicker that loading the whole module.

Also you can then use

print header;

instead of print "Content-type bla...";

HTML tags like <b> and <i> won't be escaped anyway will they?

If so when you pull the data back from the database you could just use...

s/\\(.)/$1/g;

to remove the escaping...

I may be barking up the wrong tree but also the gurus may (will) give a better answer.

Am I just being way too fussy?

EDIT:
I think I missed the point.

Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

Well, using

use CGI qw/escapeHTML unescapeHTML/;

and then

$hash{answer} = escapeHTML($hash{answer});

simply replaces all < with the ascii code, all > with the ascii code and all & etc.

The problem is this: The docs being submitted should allow html for formatting, BUT these docs also have html to display to users as in

Q. How do I use an image tag.
A.
This is how you use an image tag.
<img src="images/whatever.gif">

I want to display the SOURCE code for the image tag, but I want them to be able to format the Questions and Answers, bold the question, bullet lists etc.

The only solution I can come up with is regex replacing special codes for formatting.

This is no big deal, I don't wan't anyone doing my work for me! I appreciate everyone pointing me in the right direction! All the great docs you guys pointed out were fantastic.

I'm going back to Books a Million tonight to read a few more chapters in the regex book ( Which I will buy soon I think )..

My main problem is I've been coding in php for a while now and my limited experince with perl is a bit rusty :) I keep trying to use echo instead of print and function instead of sub .. heh.



-----------
Crowe (crowe@lit.org)

Oh I see what you mean....

Hm.....yeah what you could do is enter the html as and (spaces are there so the forum doesn't mess it up) or whatever so it isn't escaped and then use...

s/(\[)(\/?.)(\])/<$2>/gi;

To return it back to the proper html.

That took me a while to figure out so pleeease don't diss it




Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

This is too much fun :)

Okay, this is working PERFECT and is a fine solution to my problem:

$hash{answer} =~ s/(\[BOLD\])/\<b>/g;
$hash{answer} =~ s/(\[\/BOLD\])/\<\/b>/g;
$hash{answer} =~ s/(\[LI\])/\<LI>/g;
$hash{answer} =~ s/(\[\/LI\])/\<\/LI>/g;

But my question is this, I've tried and I keep getting the syntax wrong. Is there a way to do this in 1 line with a grouping of some sort? I've been browsing perldoc, cpan and using the books at hand (perl black book, cook book and programming perl ).. I'm close, I can feel it ;)

--Chris





-----------
Crowe (crowe@lit.org)

Check my code above.....that will work for things like and other single letter tags.

For single, two, three and four letter tags use.....

s/(\[)(\/?..?.?.?)(\])/<$2>/gi;

Note that the square bracket tag must be the same as the output required....



Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

I'm not going to diss that. I'm having trouble fathoming it!
I'm going to try to understand it now. I'll be back in a few ..heh.


-----------
Crowe (crowe@lit.org)

Ok hope this explanation helps......

s/(\[)(\/?.)(\])/<$2>/gi;

(\[) checks for a square bracket in a string at assigns it to $1

(\/?.) looks for /'s (used in the closing tag) but ignores it if it isn't there because it is optional due the the ? Then the final dot matches one character. The value is assigned to $2

(\]) checks for the closing bracket and is assigned to $3

So now you have a left bracket in $1 the text in $2 and the closing bracket in $3, so <$2> is just replacing the square brackets with angled brackets and puts back the tag value stored in $2

Make any sense?...lol

Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

Your way is just beautiful. Artistic even.

Using your code I came up with this, trying to figure out how it works, which this works btw.

$hash{answer} =~ s/(\[(BOLD|\/BOLD|I|\/I|LI)\])/\<$2>/gi;

But using your code that time like this:

$hash{answer} =~ s/(\[)(\/?.?.?.?.)(\])/<$2>/gi;

Works even better. Although I'm going to drop the /gi; and make it just /g; .. I want it to be case sensative :)

Your help ( and everyone's ) has been great as usual. And I hope I wasn't using up this forum's resources to much with my project here. As I've seen posted so many times and I am a firm believer in, Search at google.com, deja.com, cpan, perldoc and then some, search the board then start over ;) ..

Again, Thanks a million for the help!



-----------
Crowe (crowe@lit.org)

Hi,

No problem - it has been fun :).....

I think you can make it even shorter though.....

$hash{answer} =~ s/(\[)(\/?..+?)(\])/<$2>/g;



Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/