Page 1 of 1

PHP & Sqlite Question

Posted: Thu Feb 21, 2008 2:11 pm
by SpecialK
I am using an Sqlite database and can't find anything how to use escaped quotes when inserting.

Prior to the query, I call addslashes on the variables

Code: Select all

 
$article = addslashes($article);
$query = "INSERT INTO news (date,title,article) VALUES ('$date','$title','$article')";
 
Apparently sqlite doesn't like this way of escaping characters, so what would be the best solution? I need to be able to insert single and double quotes, so am a bit stumped when in comes to the sql and php combination.

Re: PHP & Sqlite Question

Posted: Thu Feb 21, 2008 2:17 pm
by Christopher
Each database has its own escaping function. You need to always use the database specifc one.

http://us.php.net/manual/en/function.sq ... string.php

Re: PHP & Sqlite Question

Posted: Thu Feb 21, 2008 2:37 pm
by SpecialK
Thanks!

I never knew about this command previously, definately a huge help for the future

Re: PHP & Sqlite Question

Posted: Thu Feb 21, 2008 3:41 pm
by Stryks
What version of sqlite are you trying to use?

If you use 3 (apparently faster but not without issues) you need to use PDO, and can use the prepare() statement to automatically escape values and lock down queries.

Re: PHP & Sqlite Question

Posted: Thu Feb 21, 2008 3:59 pm
by SpecialK
This is currently version 2, but the new server to migrate to in the coming while will be version 3.
I'm not looking forward to that as I don't really like the differences of PDO, but I may come to like it more with actual use.

Re: PHP & Sqlite Question

Posted: Thu Feb 21, 2008 5:53 pm
by alex.barylski
SpecialK wrote:This is currently version 2, but the new server to migrate to in the coming while will be version 3.
I'm not looking forward to that as I don't really like the differences of PDO, but I may come to like it more with actual use.
PDO === PITA :P

Re: PHP & Sqlite Question

Posted: Fri Feb 22, 2008 5:50 am
by Stryks
Hockey wrote:PDO === PITA :P
Totally with you on that. I never DID find a way to return affected rows.

Definite PITA, but I did run a few comparison tests when I was deciding which version to use and in my 'real-world' testing, V3 was the definite winner when it came to speed. As I mentioned previously, this speed difference is apparently the norm.

In my case, the speed was more important than ... well .. ease of use or full functionality. :lol:

Re: PHP & Sqlite Question

Posted: Fri Feb 22, 2008 9:19 am
by liljester
Stryks wrote:
Hockey wrote:PDO === PITA :P
Totally with you on that. I never DID find a way to return affected rows.

Code: Select all

$update = $db->prepare("UPDATE tabe SET column = 'value';");
$update->execute();
print $update->rowCount();
i believe that will do it for you :)

Re: PHP & Sqlite Question

Posted: Sat Feb 23, 2008 1:08 am
by Stryks
liljester wrote: i believe that will do it for you :)
You'd think so, but it doesn't. It returns 0. It always returns 0, even when I have results that prove otherwise.

I'll be picking that project up again soon, and I'm thinking I might update my php install. Maybe I just had a version with issues. (if anyone is going to, it'll be me :roll: )