* * *

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

HatForCat

  • Sr. Member
  • ****
  • Posts: 260
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

  • Full Member
  • ***
  • Posts: 115
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: 260
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: 537
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: 260
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.

mgear

  • Newbie
  • Posts: 4
Re: SQLite handling quotes within quotes
« Reply #5 on: April 29, 2017, 07:39:06 pm »
Probably is not the best idea all in all to to pass the variables within the text of the query. There are argument placeholders there and it's the much more strict and portable practice.

Of course QuotedStr() is ok just here but it cultivates the bad programming habits, because, generally, database (I mean real database, not SQLite :)) is not obliged to accept the exact value of the big or the complex object by text, so, it wouldn't we saved at all or would be saved broken. The database is always possible to accept it as the query parameter, though. So it is THE good habit to use the parameters ALWAYS when you use the variables in the queries.

Code: Pascal  [Select]
  1. SQLQuery1.SQL.Text := 'insert into mytable values (?,?)';
or
Code: Pascal  [Select]
  1. SQLQuery1.SQL.Text := 'insert into mytable values (:param1,:param2)';

and, later, pass the parameters by name or by order:

Code: Pascal  [Select]
  1. SQLQuery1.ParamByName('param1').AsInteger := MyIntVariable;
  2. SQLQuery1.Params[1].AsString := MyStringVariable;
  3. SQLQuery1.ExecSQL;
« Last Edit: April 29, 2017, 07:40:39 pm by mgear »

 

Recent

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