Re: [sqlite] quoting strings issue

[Dennis Cote]

Clark Christensen wrote:


IOW, something like.

$sql = "update t1 set a = ?";
$string = $dbh->quote( qq(some long string; has many
'single quotes') );
$sth = $dbh->prepare($sql);
$rc = $sth->execute($string);

will probably eliminate both the prepare() error, and an
UPDATE error later.

-Clark


 


Clark,

The arguments passed to sqlite as parameters should not be quoted. These 
strings do not pass through the parser, they are used as literal values 
when the SQL statement is executed. If you do quote this string, the 
quotes will be included in the value of field a in your database.


You'll have to excuse my PERL (it's not a language I use)... but if 
arguments to execute() are passed as parameters to sqlite then you 
should do something like this.


$sql = "update t1 set a = ?";
$string = "some long string; has many 'single quotes'";
$sth = $dbh->prepare($sql);
$rc = $sth->execute($string);

HTH
Dennis Cote

Re: [sqlite] quoting strings issue

[Clark Christensen]

--- Puneet Kishor <[EMAIL PROTECTED]> wrote:

> While I await some insight into my previously posted
> "database locking" 
> problem, I have a question regarding quoting text.
> 
> Does SQLite have any issues with anything other than
> single-quotes? For 
> example, with colon, or semi-colon?
> 
> For example, I find occasional complaints if I try to
> update/insert a 
> string like --
> 
> $text = 'Some long-winded text with lot''s of different
> things like:
> 
> * lists
> * more lists
> 
> text with semi-colon; and even such: stuff.';
> 
> UPDATE tbl SET col = $text WHERE ...
> 
> causes error to effect that sql prepare failed (again,
> Perl/DBI 
> specific errors).
> 
> 
> --
> Puneet Kishor
> 
> 
I think your $dbh->prepare() would be likely to fail if the
value you're updating isn't properly quoted for the SQL
statement.

The Perl DBI lets you get past this to some extent by
allowing you to use replaceable vars in the prepared
statement, and passing those vars as args to
$dbh->execute().  DBI also offers a quote() method against
the $dbh that could help avoid problems executing a
prepared stmt.

IOW, something like.

$sql = "update t1 set a = ?";
$string = $dbh->quote( qq(some long string; has many
'single quotes') );
$sth = $dbh->prepare($sql);
$rc = $sth->execute($string);

will probably eliminate both the prepare() error, and an
UPDATE error later.

 -Clark