Re: [sqlite] [Delphi] Escaping quote?
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 [EMAIL PROTECTED]
> -
>
>
>
--
View this message in context:
http://www.nabble.com/-Delphi--Escaping-quote--tf3983235.html#a12158672
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] [Delphi] Escaping quote?
>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 [EMAIL PROTECTED] -
Re: [sqlite] [Delphi] Escaping quote?
"Clay Dowling" <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] [Delphi] Escaping quote?
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 [EMAIL PROTECTED] -
Re: [sqlite] [Delphi] Escaping quote?
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 [EMAIL PROTECTED]
-
Re: [sqlite] [Delphi] Escaping quote?
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 [EMAIL PROTECTED]
-
Re: [sqlite] [Delphi] Escaping quote?
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]
-
Re: [sqlite] [Delphi] Escaping quote?
>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 [EMAIL PROTECTED]
-
Re: [sqlite] [Delphi] Escaping quote?
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 [EMAIL PROTECTED]
-
[sqlite] [Delphi] Escaping quote?
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 [EMAIL PROTECTED]
-
