Hi,
I'm trying to develop a website which will accept user written input in a form and will store it in a MySQL database, and will have no problems if the user written text has a ' in it. For this I was referred to this site:

http://www.developer.com/db/print.php/10920_2184681_6

and it looks pretty easy, what I have to do.
The problem is, when I use $dbh->quote, from my error logs I can see that it is substituting a ' with \\', ie. "O'Reilly" becomes "O\\'Reilly", instead of "O\'Reilly", and I can't tell why.

Can someone help?

Thanks.

I have always used prepare, this might work better for you.

Yeah, I use prepare too, but I'm not sure what you're doing up there - what does $sth->execute($last_name, $user_name); do?

It properly quotes and substitutes those variables into the query (where the ?s are) and runs the query. Check out the documentation for a broader range of info -- http://search.cpan.org/~timb/DBI/DBI.pm

OK, but I'm trying to use quote on data input by a form on an HTML page, so I can't use prepare. The page you suggested says this:
"For others it may return something like 'Don\'t' ", which I'm not getting.
So I'm still stuck at square one .

Thanks for your help anyway.

I have written database-driven web apps using Perl and MySQL that deal with data input from the user via form. The DBI prepare and execute methods have always worked for me; so the issue may be in your code. Post your code.

I can also say I've used dbh->quote() extensively and its always worked AFAICR.
I agree, pls show your code.

OK, it's big but you asked for it (I'm posting what I think are the relevant parts) :

#! /usr/bin/perl

use DBI ;
use strict ;

use CGI ;
my ($cgi) = new CGI ;
my ($firstname) ;
my ($middlename) ;
my ($lastname) ;
my ($flat) ;
my ($street) ;
my ($city) ;
my ($state) ;
my ($country) ;
my ($postcode) ;
my ($phone) ;
my ($photo) ;
my ($type) ;
my ($rating) ;





$type = $cgi->param("type");





my ($dsn)="DBI:mysqlr:localhost";
my ($user_name) = "torrent" ;
my ($password) = "hello" ;
my ($dbh,$sth) ;
my (@ary) ;


$dbh = DBI->connect ($dsn, $user_name, $password, { RaiseError => 1 });


my ($firstname) = $cgi->param("firstname") ;
$firstname =~ s/(\w+)/\u\L$1/g;
$firstname = $dbh->quote( $firstname ) ;


, then later there's this:

$sth=$dbh->prepare (qq{ INSERT INTO people VALUES (NULL,'$firstname','$middlename','$lastname','$flat','$street','$city','$state','$country','$postcod e','$phone','$type','$rating')
$sth->execute ();

As I suspected, you are using prepare incorrectly. Take a look at my example and explanation.

This is how your code should be:

It doesn't work, first of all it's refusing to take NULL as an argument, so I had to change that, after that it works (I had to change another INSERT in my code, further down the line). Do you know how I can put in a curdate() (in MySQL) into the $sth->execute line? ie. like this:
$sth->execute ($ary[0],$reviewername,$topic,$review, curdate()) ;
, but that's not the correct syntax, do you know what is?


Thanks a LOT for your help, btw. But it's still a niggling thought in my brain as to why $dbh->quote didn't work, I was hoping for chrism01's input on that - do you know why, chrism?