Passing A delphi Wildcard Search String to MSQuery.text

[Jerbear]

Hi Guys.
A Delphi Dabbler here. Teaching myself. Using Delphi 2007 with SDAC version 4.30.0.12
purchased back in 2008 I believe.

Trying to write a small app that will allow me to search a SQL database of 30,000 records.
Search has to locate data in 8 different fields looking for a match on user's input which could
be a partial name, partial postcode, address etc.

I have the code written to produce the query which works in SQL Management Studio but not when passed
as a parameter in Delphi code.

This is the Procedure that creates the Query.

Code: Select all

procedure TMainForm.btnSearchClick(Sender: TObject);
begin
  MSQuery1.SQL.Clear;
  StrToFind := edtSearchBox.Text;
  SQLQuery1 :=
  'SELECT CaseCode, Name, Add5, convert(varchar(12),'+
  ' deb_date,103) as deb_date,'+
  ' convert(varchar(12),Start,103) as Start,'+
  ' convert(varchar(12), appdate,103) as appdate,'+
  ' (select top 1 nominee from ips_casestaff as s'+
  ' where s.casecode = ips_case.casecode and'+
  ' role = 10 and nomineetype = 1) as ''IP'','+
  ' (select top 1 nominee from ips_casestaff as s'+
  ' where s.casecode = ips_case.CaseCode and'+
  ' role = 30 and nomineetype = 1) as ''Manager'' from ips_case'+
  ' where ( casecode like %'+StrToFind+'% or name like %'+StrToFind+
  '% or add1 like %'+StrToFind+'% or add2 like %'+StrToFind+
  '% or add3 like %'+StrToFind+'% or add4 like %'+StrToFind+
  '% or add5 like %'+StrToFind+'% ) order by casecode';
  MSQuery1.SQL.Add(SQLQuery1);
  ShowMessage(MSQuery1.SQL.Text);  //Displays OK in ShowMessage window.
  MSQuery1.Execute;
end;

This ShowMessage displays what looks to me like a proper query but I get the following error.



Leaving out the left hand % wildcard the error is the same except it relates to the 'or' statement
and not the value of the StrToFind variable.

If I leave out both % wildcard characters it does this.......

[Jerbear]

Never Mind.

Worked it out eventually.
A good nights sleep and the brain is working again.
Modified this line and inserted a couple of extra quote marks, shown in RED..

StrToFind := '''%'+edtSearchBox.Text+'%''';

[hsvandrew]

Hi Jerbear,

You need to send all user queries to an SQL server using Parameters rather than constructing a string as you have done. Google 'SQL injection' for the reasons why.

query.sql.text := 'select * from MyTable where CustomerName LIKE :p1';
query.ParamByName( 'p1' ).value := searchBox.text;