Re: [sqlite] [Delphi] Escaping quote?

[John Elrick]

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 [EMAIL PROTECTED]
-----------------------------------------------------------------------------