[Sabot]

Hi, I got problem with inserting text string containing quotes to SQLite database via PHP. If single quotes are present, it generates an error: [unrecognized token: "\"], despite text didn't contain '\' character. If I use double quotes, it will insert text, but will add backslash before every double quote mark.
Another thing with backslash is, if I insert one, it will add one extra before it.
Is there any way to come around this and make it work properly?
Above problems AFAIK does not exist with MySQL, so why SQLite makes it harder?
Thanks for help in advance.

that sounds like a php magic quotes issue. can you post the code from the page that is giving you issues?

[Sabot]

Thanks for your interest, it looks that you're right.
Code is simple:

PHP Code:

$db = new SQLiteDatabase("notebook.sdb"); 

$query = "BEGIN; INSERT INTO version (date,active,category,entry)    VALUES ('".$date."','".$_POST['active']."','".$_POST['category']."','".$_POST['entry']."'); COMMIT;";

$db->query($query); 



// and this is processed query (the one causing error):

$query = "BEGIN; INSERT INTO version (date,active,category,entry) VALUES ('2007-05-30 08:01','x','NOTE','test \'test\' test'); COMMIT;"

// same if I use double quotes (no error but slashes inserted as well):

$query = "BEGIN; INSERT INTO version (date,active,category,entry) VALUES ('2007-05-30 08:03','x','NOTE','test \"test\" test'); COMMIT;" 

I've checked MySQL, it works in exactly the same way with exception that these extra backslashes are dumped upon insertion. From what I've read, I guess that in case of SQLite single quote mark shall be add before single quote mark and backslash before double quote and backslash itself, anyway, why SL don't escape them automatically?

So, I have to use php stripslashes function or try something else?

BTW I use version 2.8 on win xp.

Cheers

im not familiar with sqllite....

is there a reason to not use mysql or an equivalent pgsql/mssql?

[Sabot]

I prefer to use SQLite for speed and simplicity, please see my other thread: http://www.programmingtalk.com/showthread.php?t=38289

Well, I used stripslashes(), does it’s job, but now SQLite generates syntax error for single quotes…

referencing hte other post, why does each user require 10 tables a piece? what about using a single set of tables, with a user id attached to each row for identification??

can you post the php code that is generating the sql? i might have an idea for this one...

[Sabot]

Php code is the same as posted above but with stripslahes function:

PHP Code:

$db = new SQLiteDatabase("notebook.sdb"); 

$query = "BEGIN; INSERT INTO version (date,active,category,entry) VALUES ('".$date."','".$_POST['active']."','".$_POST['category']."','".$_POST['entry']."'); COMMIT;";

$query = stripslashes($query);

$db->query($query); 



// so query looks like:

BEGIN; INSERT INTO version (date,active,category,entry) VALUES ('2007-05-31 18:50','x','NOTE','test 'test' test'); COMMIT; 

And now gives syntax error if single quote mark is present. Although is kinda logical, it would go with mysql with no problem. It must be some way to do this, otherwise it would make sqlite not compatible with english language (don't, can't, etc).

About other post, it's not possible due to amount of stored data, and if it was speed would suffer a lot. Would you run blog site using only one table? I could do another trick, merge these tables and make it one per user but it means load of NULL fields which is no good.

As per database choice, check this speed comparison: http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison So, I'll go for sqlite, is simple and I belive simplicity is genius. You don't buy Aston Martin to get to corner shop, nor choose scooter to travel around the world. Right tool for right job.

Just this single quote bugs me...

what i would add is a replace function to the line or call the addslashes function

$query = "BEGIN; INSERT INTO version (date,active,category,entry) VALUES ('".$date."','".$_POST['active']."','".$_POST['category']."','".$_POST['entry']."'); COMMIT;"; 
 

that will go through and replace the single quotes with the escape character


in answer to your question about the blog site, yes i would use a single table with each row having both a dedicated primary key as well as having a user id key, there is no reason to create the same database 10 times for 10 users if they will all have the same fields....just my thinking....

[mab]

I think reading the sqllite_escape_string function description will help -

sqlite_escape_string() will correctly quote the string specified by item for use in an SQLite SQL statement. This includes doubling up single-quote characters (') and checking for binary-unsafe characters in the query string.

A single-quote within the data needs to be made into a literal by using two consecutive single-quotes.

koncept, for some reason the code highlighting did not work in your post above...

Edit: This warning is under the sqllite_escape_string function as well -

Warning
addslashes() should NOT be used to quote your strings for SQLite queries; it will lead to strange results when retrieving your data.

Which means that any (all) data being inserted using sqllite needs to have stripslashes() applied and then sqllite_escape_string() applied to it.

[Nico]

koncept, for some reason the code highlighting did not work in your post above...

He forgot the t in highlight. I added it.