[Delphi] Escaping quote?

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

[Delphi] Escaping quote?

Gilles Ganault
Hello

        I'm having a problem saving strings into a colum from a Delphi application
because they might contain the ( ' ) single quote character:

=========
// Input := 'Let's meet at the pub tonight!';
MyFormat := 'insert into stuff (title) values ('''%s')';
SQL := Format(MyFormat, Input);

try
     ASQLite3DB1.Database := db;
     ASQLite3DB1.DefaultDir := ExtractFileDir(Application.ExeName);
     ASQLite3DB1.Open;

     ASQLite3DB1.SQLite3_ExecSQL(SQL);
     ASQLite3DB1.Close;
except
     ShowMessage('Bad');
end;
=========

Is there a function I should call either in SQLite or Delphi before running
the SQL query?

Thank you.


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: [Delphi] Escaping quote?

John Elrick-2
Gilles Ganault wrote:

> Hello
>
>     I'm having a problem saving strings into a colum from a Delphi
> application because they might contain the ( ' ) single quote character:
>
> =========
> // Input := 'Let's meet at the pub tonight!';
> MyFormat := 'insert into stuff (title) values ('''%s')';
> SQL := Format(MyFormat, Input);
>
> try
>     ASQLite3DB1.Database := db;
>     ASQLite3DB1.DefaultDir := ExtractFileDir(Application.ExeName);
>     ASQLite3DB1.Open;
>
>     ASQLite3DB1.SQLite3_ExecSQL(SQL);
>     ASQLite3DB1.Close;
> except
>     ShowMessage('Bad');
> end;
> =========
>
> Is there a function I should call either in SQLite or Delphi before
> running the SQL query?

// Input := 'Let's meet at the pub tonight!';
MyFormat := 'insert into stuff (title) values (%s)';
SQL := Format(MyFormat, QuotedStr(Input));

try
    ASQLite3DB1.Database := db;
    ASQLite3DB1.DefaultDir := ExtractFileDir(Application.ExeName);
    ASQLite3DB1.Open;

    ASQLite3DB1.SQLite3_ExecSQL(SQL);
    ASQLite3DB1.Close;
except
    ShowMessage('Bad');
end;


John

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: [Delphi] Escaping quote?

Ralf Junker
In reply to this post by Gilles Ganault

>I'm having a problem saving strings into a colum from a Delphi application because they might contain the ( ' ) single quote character:
>
>Is there a function I should call either in SQLite or Delphi before running the SQL query?

Why don't you use the '%q' operator of SQLite's sqlite3_mprintf? You can use sqlite3_mprintf with Delphi 6 or later. It is interfaced as a varargs function in DISQLite3, for example (http://www.yunqa.de/delphi/sqlite3/). The varargs directive allows to pass a variable number of arguments to sqlite3_mprintf, similar to Delphi's array of const declaration.

Here is a Delphi example:

//------------------------------------------------------------------------------

program SQLite3_printf;

{$APPTYPE CONSOLE}

uses
  DISQLite3Api;

var
  Input: PAnsiChar;
begin
  Input := 'Let''s meet at the pub tonight!';

  WriteLn('sqlite3_mprintf:');
  WriteLn(sqlite3_mprintf('insert into stuff (title) values (''%q'')', Input));
  WriteLn;

  WriteLn;
  WriteLn('Done - Press ENTER to Exit');
  ReadLn;
end.

//------------------------------------------------------------------------------

This is the relevant section from the sqlite3_mprintf C documentation:

The %q option works like %s in that it substitutes a null-terminated string from the argument list. But %q also doubles every '\'' character. %q is designed for use inside a string literal. By doubling each '\'' character it escapes that character and allows it to be inserted into the string.

For example, so some string variable contains text as follows:

  char *zText = "It's a happy day!";

One can use this text in an SQL statement as follows:

  char *zSQL = sqlite3_mprintf("INSERT INTO table VALUES('%q')", zText);
  sqlite3_exec(db, zSQL, 0, 0, 0);
  sqlite3_free(zSQL);

Because the %q format string is used, the '\'' character in zText is escaped and the SQL generated is as follows:

  INSERT INTO table1 VALUES('It''s a happy day!');  


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: [Delphi] Escaping quote?

John Elrick-2
In reply to this post by Gilles Ganault
Ralf Junker wrote:

>> I'm having a problem saving strings into a colum from a Delphi application because they might contain the ( ' ) single quote character:
>>
>> Is there a function I should call either in SQLite or Delphi before running the SQL query?
>>    
>
> Why don't you use the '%q' operator of SQLite's sqlite3_mprintf? You can use sqlite3_mprintf with Delphi 6 or later. It is interfaced as a varargs function in DISQLite3, for example (http://www.yunqa.de/delphi/sqlite3/). The varargs directive allows to pass a variable number of arguments to sqlite3_mprintf, similar to Delphi's array of const declaration.
>
> Here is a Delphi example:
>
> //------------------------------------------------------------------------------
>
> program SQLite3_printf;
>
> {$APPTYPE CONSOLE}
>
> uses
>   DISQLite3Api;
>
> var
>   Input: PAnsiChar;
> begin
>   Input := 'Let''s meet at the pub tonight!';
>
>   WriteLn('sqlite3_mprintf:');
>   WriteLn(sqlite3_mprintf('insert into stuff (title) values (''%q'')', Input));
>   WriteLn;
>
>   WriteLn;
>   WriteLn('Done - Press ENTER to Exit');
>   ReadLn;
> end.
>
> //------------------------------------------------------------------------------
>
> This is the relevant section from the sqlite3_mprintf C documentation:
>
> The %q option works like %s in that it substitutes a null-terminated string from the argument list. But %q also doubles every '\'' character. %q is designed for use inside a string literal. By doubling each '\'' character it escapes that character and allows it to be inserted into the string.
>
> For example, so some string variable contains text as follows:
>
>   char *zText = "It's a happy day!";
>
> One can use this text in an SQL statement as follows:
>
>   char *zSQL = sqlite3_mprintf("INSERT INTO table VALUES('%q')", zText);
>   sqlite3_exec(db, zSQL, 0, 0, 0);
>   sqlite3_free(zSQL);
>
> Because the %q format string is used, the '\'' character in zText is escaped and the SQL generated is as follows:
>
>   INSERT INTO table1 VALUES('It''s a happy day!');  
>  

Question, does the %q operator offer any advantages over calling QuotedStr ?


John Elrick

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: [Delphi] Escaping quote?

Clay Dowling
In reply to this post by John Elrick-2

John Elrick wrote:

> // Input := 'Let's meet at the pub tonight!';
> MyFormat := 'insert into stuff (title) values (%s)';
> SQL := Format(MyFormat, QuotedStr(Input));
>
> try
>     ASQLite3DB1.Database := db;
>     ASQLite3DB1.DefaultDir := ExtractFileDir(Application.ExeName);
>     ASQLite3DB1.Open;
>
>     ASQLite3DB1.SQLite3_ExecSQL(SQL);
>     ASQLite3DB1.Close;
> except
>     ShowMessage('Bad');
> end;

A much better solution than QuotedStr is to use queries with parameters.
If you're going to be running the query multiple times it also gives you a
speed boost.

Clay
--
Simple Content Management
http://www.ceamus.com


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: [Delphi] Escaping quote?

John Elrick-2
Clay Dowling wrote:

> John Elrick wrote:
>
>  
>> // Input := 'Let's meet at the pub tonight!';
>> MyFormat := 'insert into stuff (title) values (%s)';
>> SQL := Format(MyFormat, QuotedStr(Input));
>>
>> try
>>     ASQLite3DB1.Database := db;
>>     ASQLite3DB1.DefaultDir := ExtractFileDir(Application.ExeName);
>>     ASQLite3DB1.Open;
>>
>>     ASQLite3DB1.SQLite3_ExecSQL(SQL);
>>     ASQLite3DB1.Close;
>> except
>>     ShowMessage('Bad');
>> end;
>>    
>
> A much better solution than QuotedStr is to use queries with parameters.
> If you're going to be running the query multiple times it also gives you a
> speed boost.
>  

True, however, that assumes you will be running the query multiple times
in a row, which I haven't experienced in our particular project.


John

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: [Delphi] Escaping quote?

Clay Dowling

John Elrick wrote:

>> A much better solution than QuotedStr is to use queries with parameters.
>> If you're going to be running the query multiple times it also gives you
>> a
>> speed boost.
>>
>
> True, however, that assumes you will be running the query multiple times
> in a row, which I haven't experienced in our particular project.

Even if you aren't running the query multiple times, the parametric query
is a good idea.  It avoids any possibility of SQL injection, due either to
malicious users or programming mistakes.

Clay
--
Simple Content Management
http://www.ceamus.com


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: [Delphi] Escaping quote?

D. Richard Hipp
"Clay Dowling" <[hidden email]> wrote:

> John Elrick wrote:
>
> >> A much better solution than QuotedStr is to use queries with parameters.
> >> If you're going to be running the query multiple times it also gives you
> >> a
> >> speed boost.
> >>
> >
> > True, however, that assumes you will be running the query multiple times
> > in a row, which I haven't experienced in our particular project.
>
> Even if you aren't running the query multiple times, the parametric query
> is a good idea.  It avoids any possibility of SQL injection, due either to
> malicious users or programming mistakes.
>

It is also faster, even if you are only doing the query once.
--
D. Richard Hipp <[hidden email]>


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: [Delphi] Escaping quote?

Ralf Junker
In reply to this post by John Elrick-2

>Question, does the %q operator offer any advantages over calling QuotedStr ?

Yes: The %q operator just duplicates internal quotes, it does insert quotes at the beginning and the end of the string like QuotedStr does. You can can still use sqlite3_mprintf's %Q operator for that.

Ralf  


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: [Delphi] Escaping quote?

MaxGyver
In reply to this post by Gilles Ganault
I'm not an expert on Aducom SQLite components, but anyway i'll try to help.

Maybe you should consider using parameters in your query. Parameters in SQLite start with ':', '@' or '?', however ASGSQlite supports only ':' in my opinion. Your SQL query should look like this:
INSERT INTO Stuff (Title) VALUES (:Title);

To use params in ASGSQLite:

with TASQLite3query.Create(nil) do
try
  Connection := ASQLite3DB1;
  // Force parsing of SQL. You don't have to do this since this property is False by default.
  // It's only to emphasize that this property must be set to False.
  RawSQL := False;
  // Set command text (it automatically parses SQL into Params collection).
  SQL.Text := 'INSERT INTO Stuff (Title) VALUES (:Title)';
  // Set param values
  Params.ParamByName('Title') := 'Let''s meet at the pub tonight!';
  // execute SQL
  ExecSQL;
finally
  Free;
end;


Another way is to execute SQL directly with SQLite3_Execute() method of TASQLite3DB. If so you have to create Params collection by your own.

Never compiled or tested the code above, use it at your own risk. I hope I helped a bit.

Gilles Ganault wrote
Hello

        I'm having a problem saving strings into a colum from a Delphi application
because they might contain the ( ' ) single quote character:

=========
// Input := 'Let's meet at the pub tonight!';
MyFormat := 'insert into stuff (title) values ('''%s')';
SQL := Format(MyFormat, Input);

try
     ASQLite3DB1.Database := db;
     ASQLite3DB1.DefaultDir := ExtractFileDir(Application.ExeName);
     ASQLite3DB1.Open;

     ASQLite3DB1.SQLite3_ExecSQL(SQL);
     ASQLite3DB1.Close;
except
     ShowMessage('Bad');
end;
=========

Is there a function I should call either in SQLite or Delphi before running
the SQL query?

Thank you.


-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe@sqlite.org
-----------------------------------------------------------------------------