* * *

Author Topic: SQLite handling quotes within quotes  (Read 243 times)

HatForCat

  • Sr. Member
  • ****
  • Posts: 251
SQLite handling quotes within quotes
« on: April 19, 2017, 09:50:12 pm »
I have some incoming text that has quotations within it and SQLite is seeing them as terminators for an Insert.

I load up a TStringList with incoming text then pass it to the dm.In.SQL.Add(... function
Code: Pascal  [Select]
  1. {  ...Incoming TStringList item #3
  2. <div dir="ltr"><div class="gmail_default" style="font-family:tahoma,sans-serif">
  3. }
  4.   dm.In.SQL.Add('VALUES ( ');
  5.   ...
  6.   dm.In.SQL.Add(#34+strList[2]+#34+', ');
  7.   dm.In.SQL.Add(#34+strList[3]+#34+', ');
  8.   ...
  9.  

 BUT one of the " for "ltr" is being seen as the closing quote -- crash!!

How do I handle that? Is there an escape character I should be using with #34?

I have done some searching and not found anything to help, but it might be my search terms

Thanks
Acer-i5, 2.6GHz, 6GB, 256-SSD, Ubuntu 14.04-LTS, Unity Desktop, Lazarus 1.6.2, SQLite3 -- Retired: Programming for my own use for Ubuntu.

HeavyUser

  • Jr. Member
  • **
  • Posts: 98
Re: SQLite handling quotes within quotes
« Reply #1 on: April 19, 2017, 10:07:14 pm »
1) always use the complete sql not parts
2) depending on the sql server you have to either escape the quote
there is the function quotedStr that does this for single quote <'> strings (mssql,firebird etc) You can change the code for double quotes or even better parameterize the quote char.
But I hear the mysql does not support double quoting by default you need to set it.

HatForCat

  • Sr. Member
  • ****
  • Posts: 251
Re: SQLite handling quotes within quotes
« Reply #2 on: April 19, 2017, 10:20:45 pm »
Thanks, I have found SQLite3 "QUOTE" which supposed to fix it but it is not working or I am not using it correctly.

Also, I have found that using a single quote at each end would do it, providing there are no single quotes embedded in the text.

This'll look "sweet!"
'This'll look "sweet!"'  <-- Fail
"This'll look "sweet!""  <-- Fail

Code: Pascal  [Select]
  1.     'QUOTE('+#34+strList[3]+#34+'), ';
  2.  
Acer-i5, 2.6GHz, 6GB, 256-SSD, Ubuntu 14.04-LTS, Unity Desktop, Lazarus 1.6.2, SQLite3 -- Retired: Programming for my own use for Ubuntu.

LacaK

  • Hero Member
  • *****
  • Posts: 528
Re: SQLite handling quotes within quotes
« Reply #3 on: April 20, 2017, 07:31:37 am »
If you want insert content of TStringList into any character column of SQL table, then double quotes are not problem, but single quotes are.
As far as SQL requires that character values are enclosed in single quotes and embedded single quote must be escaped by two single quotes.
You can use QuotedStr() function to handle this or better parameters in SQL statement.
So try:
  dm.In.SQL.Add(QuotedStr(strList[2])+', '); ... etc.
or:
  dm.In.SQL.Add(':value, '); ... ParamByName('value').AsString := strList[2];

HatForCat

  • Sr. Member
  • ****
  • Posts: 251
Re: SQLite handling quotes within quotes
« Reply #4 on: April 20, 2017, 04:58:44 pm »
@LacaK:
@HeavyUser:

Thanks "QuotedStr" simplified it all and solved the problem.
Acer-i5, 2.6GHz, 6GB, 256-SSD, Ubuntu 14.04-LTS, Unity Desktop, Lazarus 1.6.2, SQLite3 -- Retired: Programming for my own use for Ubuntu.

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus