delphi - 相当于 php 的 mysql_real_escape_string

Question

我需要一些动态 SQL 将大量值插入数据库。

INSERT INTO table1 (a,b,c,d) VALUES (1,2,3,'string with possible quotes'),....

因为我想每批插入大约 1,000 行，所以参数实际上并不是一个选项。
在 php 中，我使用 mysql_lib 和 mysql_real_escape_string 来防止错误和 SQL 注入(inject)。

如何在 Delphi 中转义字符串值？

Answer 1

不久前，我按照有关 mysql_real_escape_string 的 MySql 文档编写了一个 delphi 等效函数。功能。

The string in from is encoded to an escaped SQL string, taking into account the current character set of the connection. The result is placed in to and a terminating null byte is appended. Characters encoded are “\”, “'”, “"”, NUL (ASCII 0), “\n”, “\r”, and Control+Z. Strictly speaking, MySQL requires only that backslash and the quote character used to quote the string in the query be escaped. mysql_real_escape_string() quotes the other characters to make them easier to read in log files

显然，这里忽略了..考虑到连接的当前字符集部分。

function StringReplaceExt(const S : string; OldPattern, NewPattern:  array of string; Flags: TReplaceFlags):string;
var
 i : integer;
begin
   Assert(Length(OldPattern)=(Length(NewPattern)));
   Result:=S;
   for  i:= Low(OldPattern) to High(OldPattern) do
    Result:=StringReplace(Result,OldPattern[i], NewPattern[i], Flags);
end;

function mysql_real_escape_string(const unescaped_string : string ) : string;
begin
  Result:=StringReplaceExt(unescaped_string,
    ['\', #39, #34, #0, #10, #13, #26], ['\\','\'#39,'\'#34,'\0','\n','\r','\Z'] ,
    [rfReplaceAll]
  );
end;