Delphi中如何为SQL查询的字符串参数添加单引号？代码修正

原Delphi代码 #

var
  strng,value,UserName : WideString;
  Query :  Smplquery;
begin
  strng := 'table';
  value := 'column';
    
  Username := 'CN=ABC[EXT] XYZ, AB=HELLO EFG,AB=SDRTY0045,AB=DW,AB=BNKM,HB=SWCD,HB=4QW,HB=PKL';
  Query := CreateSmplquery();
    
  Query.SelectStatement:= format('select * from %s where CONVERT(VARCHAR,%s'+') = (select convert(varchar,%s'+'))',[strng, value, UserName]);
  Query.Run;
end;

当前生成的错误SQL（语法问题：字符串未被单引号包裹） #

select *
from table
where CONVERT(VARCHAR,column) = (select convert(varchar,CN=ABC[EXT] XYZ, AB=HELLO EFG,AB=SDRTY0045,AB=DW,AB=BNKM,HB=SWCD,HB=4QW,HB=PKL))

目标SQL语句 #

select *
from table
where CONVERT(VARCHAR,column) = (select convert(varchar,'CN=ABC[EXT] XYZ, AB=HELLO EFG,AB=SDRTY0045,AB=DW,AB=BNKM,HB=SWCD,HB=4QW,HB=PKL'))

修复方案 #

只需在format函数的SQL模板中，给对应UserName的占位符加上单引号（注意Delphi中字符串里的单引号需用两个单引号转义），修改后的代码如下：

var
  strng,value,UserName : WideString;
  Query :  Smplquery;
begin
  strng := 'table';
  value := 'column';
    
  Username := 'CN=ABC[EXT] XYZ, AB=HELLO EFG,AB=SDRTY0045,AB=DW,AB=BNKM,HB=SWCD,HB=4QW,HB=PKL';
  Query := CreateSmplquery();
    
  Query.SelectStatement:= format('select * from %s where CONVERT(VARCHAR,%s) = (select convert(varchar,''%s''))',[strng, value, UserName]);
  Query.Run;
end;

内容的提问来源于stack exchange，提问作者Lucas