escaping a string in query

Discussion of open issues, suggestions and bugs regarding MyDAC (Data Access Components for MySQL) for Delphi, C++Builder, Lazarus (and FPC)
vindac
Posts: 19
Joined: Tue 18 Mar 2014 16:44

escaping a string in query

Post by vindac » Fri 22 Jul 2016 09:29

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
Devart Team
Posts: 2793
Joined: Wed 30 Jul 2014 07:16

Re: escaping a string in query

Post by ViktorV » Fri 22 Jul 2016 11:01

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
Posts: 38
Joined: Tue 25 Jan 2005 11:22
Location: Somerville, MA
Contact:

Re: escaping a string in query

Post by davidmarcus » Fri 22 Jul 2016 15:59

I think you can use TMySQLConnection.EscapeAndQuoteStr.

ViktorV
Devart Team
Posts: 2793
Joined: Wed 30 Jul 2014 07:16

Re: escaping a string in query

Post by ViktorV » Mon 25 Jul 2016 10:37

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

vindac
Posts: 19
Joined: Tue 18 Mar 2014 16:44

Re: escaping a string in query

Post by vindac » Wed 27 Jul 2016 13:07

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
Devart Team
Posts: 2793
Joined: Wed 30 Jul 2014 07:16

Re: escaping a string in query

Post by ViktorV » Wed 27 Jul 2016 14:03

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
Posts: 19
Joined: Tue 18 Mar 2014 16:44

Re: escaping a string in query

Post by vindac » Tue 19 Jun 2018 19:06

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
Devart Team
Posts: 2793
Joined: Wed 30 Jul 2014 07:16

Re: escaping a string in query

Post by ViktorV » Wed 20 Jun 2018 08:03

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
Posts: 19
Joined: Tue 18 Mar 2014 16:44

Re: escaping a string in query

Post by vindac » Wed 20 Jun 2018 13:53

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
Devart Team
Posts: 2793
Joined: Wed 30 Jul 2014 07:16

Re: escaping a string in query

Post by ViktorV » Wed 20 Jun 2018 15:16

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
Posts: 3
Joined: Sat 27 Apr 2019 17:33

Re: escaping a string in query

Post by capfka » Sat 27 Apr 2019 18:26

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
Devart Team
Posts: 2793
Joined: Wed 30 Jul 2014 07:16

Re: escaping a string in query

Post by ViktorV » Tue 30 Apr 2019 06:51

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
Posts: 3
Joined: Sat 27 Apr 2019 17:33

Re: escaping a string in query

Post by capfka » Tue 30 Apr 2019 07:37

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
Devart Team
Posts: 2793
Joined: Wed 30 Jul 2014 07:16

Re: escaping a string in query

Post by ViktorV » Tue 30 Apr 2019 08:09

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

avinash
Posts: 1
Joined: Wed 18 Sep 2019 10:45
Contact:

Re: escaping a string in query

Post by avinash » Wed 18 Sep 2019 10:50

Thanks for the solution. It is very helpful.

Post Reply