Re: DBI quote() seems to replace double quotes with two single quotes

Northstardomus wrote:
I'm running a script where I'm inserting some values into a MySQL
database. I'm using quote to escape values before inserting them into
the database and the problem it's causing me is that it seems to
replace double quotes with two single quotes.

That is, it replaces this character: " with two of these: ' like this:
''

In my script I do a select on all of the important fields in the table
I'm inserting into to see if I've already added this data and this
behavior is tripping up my logic to believe that I haven't already
inserted this data.

So:

1.) Is anybody familiar with this behavior?
2.) Do you think I diagnosed the issue (see below) correctly. And if I
did:
3.) Any suggestions to fix what I'm doing? The goal is to be able to
avoid duplicate records and keep running the script if a duplicate is
found.

Thanks in advance!

An example of what I'm doing:

I have a data base with a table of users, let's say:
+--------------------------------------------+
| userID | last_update | favorite_saying |
+--------------------------------------------+
| 1 | 06-05-2005 | Cool |
+--------------------------------------------+
| 2 | 09-17-2005 | Sweet |
+--------------------------------------------+
| 3 | 06-05-2005 | 12'' of snow |
+--------------------------------------------+
| 3 | 10-05-2005 | Wa wa wi wa |
+--------------------------------------------+

In this example I would compare the userID and favorite_saying values
to see if both values match, allowing users to update their favorite
saying as user #3 did after seeing the movie Borat. The problem comes
when the user decides to go back to his old saying because I would then
be comparing 12" of snow to 12'' of snow; no match and then I get a
duplicate entry.

If I didn't make it obvious enough, the values in the example database
were inserted after being escaped by quote(), i.e.:

my $dbh =
DBI->connect("DBI:mysql:database=users;host=localhost","root",
"password",{'RaiseError' => 1});
my $favorite_saying = '12" of snow';
$favorite_saying = $dbh->quote($favorite_saying);

Try using placeholders instead of quote():

my $dbh =
DBI->connect("DBI:mysql:database=users;host=localhost","root",
"password",{'RaiseError' => 1});
my $favorite_saying = '12" of snow';
my $sql = "INSERT INTO users (userID, last_update, favorite_saying)
VALUES (?, ?, ?)";
my $sth = $dbh->prepare($sql);
$sth->execute(3, '06-05-2005', $favorite_saying)
or die "Error inserting record: $DBI::errstr";

.

Relevant Pages

  • Re: Problem When Serializing Array With Multiline Text
    ... serialized data before inserting into the database? ... It does escape ... newlines, which I have never seen being a problem anyway, but it is ...
    (comp.lang.php)
  • Re: Requery "Too Soon"?
    ... "The database has been placed in a state by user ... then the Requery will return the just ... MsgBox appears, I get an empty subform, i.e., I do ... I'll try inserting a DoEvents or two. ...
    (microsoft.public.access.formscoding)
  • Re: SQLCE performance from .NET CF v2.0
    ... Please remember when bulk inserting is being executed against SQL CE, ... > local database functionality. ... > database technology and I am considering switching to SQLCE at the same ... > execute the prepared statement again. ...
    (microsoft.public.dotnet.framework.compactframework)
  • RE: Crystal Report or MS Word?
    ... Access databases and uses Crystal Reports 5.0 to generate a quote. ... modified the old VB4.0 code to allow users to save the Crystal Report ... saved in the database and the modifications made in Word are not so you ... As I upgrade this program to VB.net, I am trying to clean up this whole ...
    (microsoft.public.dotnet.languages.vb)
  • Crystal Report or MS Word?
    ... Access databases and uses Crystal Reports 5.0 to generate a quote. ... modified the old VB4.0 code to allow users to save the Crystal Report ... saved in the database and the modifications made in Word are not so you ... my application fill in a Word Template with the quote data. ...
    (microsoft.public.dotnet.languages.vb)