Current location: Hot Scripts Forums » Other Discussions » Database » Insert text with quotes to SQLite - how?

Insert text with quotes to SQLite - how?

Reply
  #1 (permalink)  
Old
Newbie Coder
 
Join Date: May 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Insert text with quotes to SQLite - how?

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.
Reply With Quote

Featured Marketplace Scripts (view more)

  #2 (permalink)  
Old
Guest
 
Posts: n/a
that sounds like a php magic quotes issue. can you post the code from the page that is giving you issues?
Reply With Quote
  #3 (permalink)  
Old
Newbie Coder
 
Join Date: May 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
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
Reply With Quote
  #4 (permalink)  
Old
Guest
 
Posts: n/a
im not familiar with sqllite....

is there a reason to not use mysql or an equivalent pgsql/mssql?
Reply With Quote
  #5 (permalink)  
Old
Newbie Coder
 
Join Date: May 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
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…
Reply With Quote
  #6 (permalink)  
Old
Guest
 
Posts: n/a
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...
Reply With Quote
  #7 (permalink)  
Old
Newbie Coder
 
Join Date: May 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
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:
BEGININSERT INTO version (date,active,category,entryVALUES ('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...
Reply With Quote
  #8 (permalink)  
Old
Guest
 
Posts: n/a
what i would add is a replace function to the line or call the addslashes function
php Code:
  1. $query = "BEGIN; INSERT INTO version (date,active,category,entry) VALUES ('".$date."','".$_POST['active']."','".$_POST['category']."','".$_POST['entry']."'); COMMIT;";
  2.  
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....

Last edited by Nico; 05-31-07 at 03:32 PM.
Reply With Quote
  #9 (permalink)  
Old
mab's Avatar
Community VIP
 
Join Date: Oct 2005
Location: Denver, Co. USA
Posts: 2,681
Thanks: 0
Thanked 4 Times in 4 Posts
I think reading the sqllite_escape_string function description will help -
Quote:
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 -
Quote:
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.
__________________
Error checking, error reporting, and error recovery. If your code does not have these to get it to tell you why it is not working, what makes you think someone in a programming forum will be able to tell you why it is not working???

Last edited by mab; 05-31-07 at 03:34 PM.
Reply With Quote
  #10 (permalink)  
Old
Nico's Avatar
Community Leader
 
Join Date: Sep 2005
Location: Spain
Posts: 8,129
Thanks: 12
Thanked 93 Times in 89 Posts
Quote:
koncept, for some reason the code highlighting did not work in your post above...
He forgot the t in highlight. I added it.
Reply With Quote
Reply

Bookmarks


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with Auto Dealer Script nuzzle PHP 17 04-14-10 09:34 PM
Search script improvement 9999 PHP 14 08-29-06 12:46 AM
Draggable Tables Ares JavaScript 10 08-03-06 07:55 AM
Need Your HelP! Loading Multiple External Text into Multiple Dynamic Text Fields Flash_Boi Flash & ActionScript 2 03-30-06 04:27 PM
picking random entries with a filter... Double selection problem dsumpter PHP 7 11-16-03 08:19 PM


All times are GMT -5. The time now is 08:13 AM.
vBulletin® Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.