Equivalent of php's mysql_real_escape_string

Question

I have a need for some dynamic SQL to INSERT a large number of values into a database.

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

Because I want to insert about a 1,000 rows per batch, parameters are not really an option.
In php I'd use the mysql_ lib and mysql_real_escape_string to prevent errors and SQL-injections.

How do I escape the string values in Delphi?

Answer 1

Sometime ago I wrote a delphi equivalent function, following the MySql Documentation about the mysql_real_escape_string function.

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

obviously the part ..taking into account the current character set of the connection is ignored here.

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;