Escaping single quotes with sql

[Wilco van der Veer]

Goodday,

I am trying to put values that may contain a ' (single quote) into a
sql database but it keeps crashing when the value contains a '. I have
tried using the quote function:

$string = "'s avond's";
my $clean_string = $dbh->quote($string);

But even then it crashes whenever a ' appears.

Does anyone have a solution for this?

Thanks,

Wilco van der Veer

 

[Jack Challen]

I am trying to put values that may contain a ' (single quote) into a
sql database but it keeps crashing when the value contains a '. I have
tried using the quote function:

$string = "'s avond's";
my $clean_string = $dbh->quote($string);

What happens if you try using placeholders (untested code ahead)?

my $query = "INSERT INTO foo VALUES(?)";
my $sth = $dbh->prepare($query);
$sth->execute($string);

Cheers
jack

 

[J. Gleixner]

Goodday,

I am trying to put values that may contain a ' (single quote) into a
sql database but it keeps crashing when the value contains a '. I have
tried using the quote function:

$string = "'s avond's";
my $clean_string = $dbh->quote($string);

But even then it crashes whenever a ' appears.

Does anyone have a solution for this?

That's one way to do it. It will work, depending on how you're using it.
Show us your code where you use $clean_string to do your insert.

 

[ctcgag]

Goodday,

I am trying to put values that may contain a ' (single quote) into a
sql database but it keeps crashing when the value contains a '. I have
tried using the quote function:

First off, "Use bind variables and place holders" is the best answer to
most of the DBI questions posted here. Yours is no exception. Use place
holders and bind variables.

$string = "'s avond's";
my $clean_string = $dbh->quote($string);

But even then it crashes whenever a ' appears.

What do mean by "crashes"? Perl produces error messages for your
benefit. Do you not think that we should at least look at them?

Does anyone have a solution for this?

Yes.

Xho

 

[Wilco van der Veer]

That's one way to do it. It will work, depending on how you're using it.
Show us your code where you use $clean_string to do your insert.

#!/usr/bin/perl

# load module
use DBI();

# connect
my $dbh = DBI->connect("DBIgPP:database=honnepon;host=$hostname", "$user",
"$pass", {'RaiseError' => 1});

$string = "'s avond's";
my $clean_string = $dbh->quote($string);

my $sth = $dbh->prepare("INSERT INTO files (filename) VALUES ('$string');");
$sth->execute();

# clean up
$dbh->disconnect();

 

[Paul Lalli]

$string = "'s avond's";
my $clean_string = $dbh->quote($string);

my $sth = $dbh->prepare("INSERT INTO files (filename) VALUES ('$string');");

quote() doesn't modify its argument, it returns a new string. $string is
exactly what it was before the call to quote(). You should be using
$clean_string in the SQL statement.

However, the 'correct' answer is still to be using place holders, as
otehrs in this thread have said.

read perldoc DBI for more info.

Paul Lalli

 

[Wilco van der Veer]

What do mean by "crashes"? Perl produces error messages for your
benefit. Do you not think that we should at least look at them?

Errorcode:

DBD:gPP::st execute failed: ERROR: parser: parse error at or near "s" at
character 40
DBD:gPP::st execute failed: ERROR: parser: parse error at or near "s" at
character 40

 

[David K. Wall]

#!/usr/bin/perl

No strictures or warnings? Or did you just cut them from your post?

# load module
use DBI();

DBI doesn't export anything, so the parentheses are unnecessary.

# connect
my $dbh =
DBI->connect("DBIgPP:database=honnepon;host=$hostname", "$user",
"$pass", {'RaiseError' => 1});

You don't need quotes around $user and $pass. See
perldoc -q quoting

$string = "'s avond's";
my $clean_string = $dbh->quote($string);

This line is not necessary if you use placeholders, as at least two
people suggested. See below.

my $sth = $dbh->prepare("INSERT INTO files (filename) VALUES
('$string');");
$sth->execute();

Better written as (rearranged to fit into an 80-character line)

my $sth = $dbh->prepare(
'INSERT INTO files (filename) VALUES (?)'
);
$sth->execute($string);

 

[Wilco van der Veer]

Better written as (rearranged to fit into an 80-character line)

my $sth = $dbh->prepare(
'INSERT INTO files (filename) VALUES (?)'
);
$sth->execute($string);

Great, that works!

Thanks alot for the help everbody!

 

[Keith Keller]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

#!/usr/bin/perl

Is it safe to assume that you have

use strict;
use warnings;

in your code?

[snip DBI code]

$string = "'s avond's";
my $clean_string = $dbh->quote($string);

my $sth = $dbh->prepare("INSERT INTO files (filename) VALUES ('$string');");

^^^^^^^
Did you mean to use the unquoted string?

Even so, if you read perldoc DBI (you did that, right?), and look for the
description of the quote method, you'll see that $clean_string should
probably already have the outside quotes. You'll probably want

my $sth = $dbh->prepare("INSERT INTO files (filename) VALUES ($clean_string)");

Or, as someone else mentioned, just use placeholders, which will allow
DBI and DBD:gPP to figure out all the quoting for you.

- --keith

- --
(e-mail address removed)-francisco.ca.us
(try just my userid to email me)
AOLSFAQ=http://wombat.san-francisco.ca.us/cgi-bin/fom

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFAv1ChhVcNCxZ5ID8RAomXAJ9QZR9q6c6i5sHNnbOCAUFoKbybTACeJ1xS
akvli8piiNRIfYmA4Ixu6y8=
=K3FM
-----END PGP SIGNATURE-----

 

[Ben Morrow]

You don't need quotes around $user and $pass. See
perldoc -q quoting

Nor around 'RaiseError'.=> autoquotes its left argument if it matches
/^\w+$/.

Ben

 

[Guest]

(e-mail address removed) (Wilco van der Veer) wrote:

First off, "Use bind variables and place holders" is the best answer
to most of the DBI questions posted here. Yours is no exception.
Use place holders and bind variables.

If available.

Not so much with DBD::Sybase and FreeTDS pointing at SQL Server.

Can't speak for the OP, but in my case, I'm stuck playing
the quote game, unpleasant as it is.

FWIW. Annoying when building DBD::Sybase - certain tests fail.

 

[David K. Wall]

Nor around 'RaiseError'.=> autoquotes its left argument if it
matches /^\w+$/.

Thanks. Somehow I missed seeing that one.

 

[Tad McClellan]

DBI doesn't export anything, so the parentheses are unnecessary.

But if the parenthesis are not there, then you have to go find
out if DBI exports anything or not.

With parens you know right away that nothing is being imported
without having to go do any research.


I'd call using those parens the "good kind" of Lazy.

 

[David K. Wall]

But if the parenthesis are not there, then you have to go find
out if DBI exports anything or not.

With parens you know right away that nothing is being imported
without having to go do any research.


I'd call using those parens the "good kind" of Lazy.

I hadn't looked at it that way. I'll have to keep that in mind for my own
use.

 

[nobull]

But if the parenthesis are not there, then you have to go find
out if DBI exports anything or not.

With parens you know right away that nothing is being imported
without having to go do any research.


I'd call using those parens the "good kind" of Lazy.

I have to disagree. Using empty parens on use is like using & on a
function call. It is a way to tell Perl that you want to suppress
part of the normal functionality. It should oly be used on special
occasions. For this reason whenever I see it I am inclined to go off
and do research to figure out what it is that is being avioded.

I'd always stick to:

use DBI;

or

require DBI;