escaping a string in query

[vindac]

Hi,
what's the best way to escape (for quotes, percent signs,...) a string for a query? Is there a global function to escape a string, or does it need to be done through the params in tmyquery?
Thanks

[ViktorV]

You can quote strings in the query of the TMyQuery component. For example:

Code: Select all

   MyQuery.SQL.Text := 'select * from dept where dname = ' + QuotedStr('ACCOUNTING');

or

Code: Select all

  MyQuery.SQL.Text := 'select * from dept where dname = ''ACCOUNTING''';

You can also use parameters:

Code: Select all

  MyQuery.SQL.Text := 'select * from dept where dname = :dname';
  MyQuery.ParamByName('dname').AsString := 'ACCOUNTING';

[davidmarcus]

I think you can use TMySQLConnection.EscapeAndQuoteStr.

[ViktorV]

You are right. For string quoting, you can also use the TMySQLConnection.EscapeAndQuoteStr method.

[vindac]

thanks for the replies!

[*]I tried with QuotedStr, but it only quotes single quotes (not double quotes). So this does not work when a query is formatted like this:

Code: Select all

q.SQL.Text := 'insert into tsmart values("'+ QuotedStr( Edit1.Text)+'");';

(I use double quotes sometimes to have a better readability in delphi, I think mysql threats them equally, no?)

[*]for TMySQLConnection.EscapeAndQuoteStr: I can't find this class, always using tmyconnection instead. And the EscapeAndQuoteStr does not seem to exist in TMyConnection

[*]Should TMySQLConnection.EscapeAndQuoteStr also handle the percent (%) sign in a lookup query?
For instance: a user looks up a product where the name contains '... 50%...' .

[ViktorV]

To quote string with any characters, you can use the AnsiQuotedStr method: http://docwiki.embarcadero.com/Librarie ... iQuotedStr
For example:

Code: Select all

q.SQL.Text := 'insert into tsmart values(' + AnsiQuotedStr(Edit1.Tex, '"') + ');';

The TMySQLConnection.EscapeAndQuoteStr class is declared in the MyClasses.pas module. But it is inadvisable for your case.

[vindac]

Hi,

I'm trying to retrieve a record from this table:

Code: Select all

mysql> select * from  tcht_file;
+----+--------+-------------------------+----------+------------------+
| id | projnr | fn                      | filetype | file_proc_status |
+----+--------+-------------------------+----------+------------------+
|  1 | P12047 | \MT_20140101_070004.zip | DBBU     | INPROGRESS       |
+----+--------+-------------------------+----------+------------------+
1 row in set (0.00 sec)

As you can see the record contains a backslash. I'm trying this with a parambyname as you suggested above, but it does not work:

Code: Select all

  FQKBTmp.SQL.Text := 'select * from tcht_file';
  FQKBTmp.SQL.Add(' where filetype = "DBBU"');
  FQKBTmp.SQL.Add(' and projnr like "'+aProjNr+'"');
  FQKBTmp.SQL.Add(' and fn like :slashedfn');
  FQKBTmp.ParamByName('slashedfn').AsString := fnZipRelative; //fnZipRelative = \MT_20140101_070004.zip
  FQKBTmp.Execute;

Shouldn't the parambyname to the quoting and escaping?

[ViktorV]

To solve your task, you should use the value

Code: Select all

 '\\MT_20140101_070004.zip'

but not

Code: Select all

 '\MT_20140101_070004.zip' 

due to using a special symbol '\' https://dev.mysql.com/doc/refman/8.0/en ... erals.html. For example:

Code: Select all

fnZipRelative := '\\MT_20140101_070004.zip';

[vindac]

Hi ViktorV,
thanks for the reply.

I tested and adding slashes manually works like this:

Code: Select all

  q.SQL.Text := 'select * from tfile where fn like "'+ReplaceStr(fnZipRelative,'\','\\\\')+'"');
  q.Execute;

When using the parambyname, it works (indeed as you say) by adding a backslash:

Code: Select all

	q.SQL.Text := 'select * from tfile ';
    q.SQL.Add('where fn like :myparam');
    q.ParamByName('myparam').AsString := ReplaceStr(fnZipRelative,'\','\\');

I am still wondering: is there a function that I can call that does all the necessary escaping when dealing with (querying/storing) user input (possibly containing percent signs,backslashes,quotes,double quotes, etc, don't know if there are other characters)?

Thanks

[ViktorV]

Using '\\\\' or '\\' with a parametrized query in the LIKE statement is a feature of MySQL functionality:
To search for \, specify it as \\\\; this is because the backslashes are stripped once by the parser and again when the pattern is made, leaving a single backslash to be matched against.
https://dev.mysql.com/doc/refman/8.0/en ... rator_like
For example, the following code will immediately return the correct result when using a parametrized request:

Code: Select all

fnZipRelative := '\MT_20140101_070004.zip';
FQKBTmp.SQL.text := ' select :slashedfn';
FQKBTmp.ParamByName('slashedfn').AsString := fnZipRelative;
FQKBTmp.Open;

Thus, to solve your issue, use parametrized queries

[capfka]

Hi, I have a problem with TMyQuery parameters which I'd like some advice on. I'm converting a customer management system which was originially written (by someone else) over MS Access but which now needs to be converted to MySQL. I'm using DAC v8.6.20 which I purchased in 2015 but have never used before.

I'm trying to convert a "search while typing" function and I know I need a parameterised query for the search function which is triggered by a TEdit.OnChange event. The Access search query is quite straightforward, but I'm finding that it's not so simple in a MyQuery. Initially I want to show all of the records to allow a user to browse through the records, but then I want to be able to use the TEdit to search while typing. In Access, the parameter for this is '% %' which will retrieve all records because there is always a space in the customer name. But I can't make this work in the MyQuery. The code I have is:

Code: Select all

  CustQuery.Close;
  Custquery.Params.CreateParam(ftString, 'paramA', ptInput);
  CustQuery.ParamByName('paramA').value := '&& &&';
  CustQuery.ParamCheck := True;
  Custquery.Open
  

But this returns no records. I understand from reading through this forum that you need multiple ampersands because of the way the parameter is parsed, but I have obviously missed something.

Also, despite the fact that I have created the parameters manually, I find if I set paramcheck to false, it errors with an assertion problem.

Can someone help me with these issues, please?

Thanks

[ViktorV]

The TMyQuery.ParamCheck property is responsible for enabling (disabling) the automatic parameters generation when changing SQL property : https://www.devart.com/mydac/docs/deva ... mcheck.htm.
To add parameters in runtime you can use the following code:

Code: Select all

  MyQuery.ParamCheck := False;
  MyQuery.SQL.Text := 'select * from dept where dname like :paramA';
  MyQuery.Params.CreateParam(ftString, 'paramA', ptInput);
  MyQuery.ParamByName('paramA').Value := '% %';
  MyQuery.Open;

Also to solve the issue you can use the next code:

Code: Select all

  MyQuery.ParamCheck := True;
  MyQuery.SQL.Text := 'select * from dept where dname like :paramA';
  MyQuery.ParamByName('paramA').Value := '% %';
  MyQuery.Open;

[capfka]

Thank you - I could have sworn that I'd tried this first. Was the position of the params check line important? Regardless thank you!

[ViktorV]

The TMyQuery.ParamCheck property is best set before setting the TMyQuery.SQL.Text property.

[avinash]

Thanks for the solution. It is very helpful.