Search code examples
mysqldelphiescaping

Delphi / MySQL: Problems escaping strings

I am having problems escaping strings. I used the QuotedStr() function - shouldn't that be enough.

Unfortunately, the string that I am trying to quote is rather messy, but I will post it here in case anyone wants to paste it into WinMerge or KDiff3, etc.

I am trying to store an entire Delphi form into the database, rather than into a .DFM file. It has only one field, a TEdit edit box.

The debugger shows the form as text as

'object Form1: TScriptForm'#$D#$A'  Left = 0'#$D#$A'  Top = 0'#$D#$A'  Align = alClient'#$D#$A'  BorderStyle = bsNone'#$D#$A'  ClientHeight = 517'#$D#$A'  ClientWidth = 993'#$D#$A'  Color = clBtnFace'#$D#$A'  Font.Charset = DEFAULT_CHARSET'#$D#$A'  Font.Color = clWindowText'#$D#$A'  Font.Height = -11'#$D#$A'  Font.Name = 'MS Sans Serif''#$D#$A'  Font.Style = []'#$D#$A'  OldCreateOrder = False'#$D#$A'  SaveProps.Strings = ('#$D#$A'    'Visible=False')'#$D#$A'  PixelsPerInch = 96'#$D#$A'  TextHeight = 13'#$D#$A'  object Edit1: TEdit'#$D#$A'    Left = 192'#$D#$A'    Top = 64'#$D#$A'    Width = 121'#$D#$A'    Height = 21'#$D#$A'    TabOrder = 8'#$D#$A'  end'#$D#$A'end'#$D#$A

before calling QuotedStr() and

''object Form1: TScriptForm'#$D#$A'  Left = 0'#$D#$A'  Top = 0'#$D#$A'  Align = alClient'#$D#$A'  BorderStyle = bsNone'#$D#$A'  ClientHeight = 517'#$D#$A'  ClientWidth = 993'#$D#$A'  Color = clBtnFace'#$D#$A'  Font.Charset = DEFAULT_CHARSET'#$D#$A'  Font.Color = clWindowText'#$D#$A'  Font.Height = -11'#$D#$A'  Font.Name = ''MS Sans Serif'''#$D#$A'  Font.Style = []'#$D#$A'  OldCreateOrder = False'#$D#$A'  SaveProps.Strings = ('#$D#$A'    ''Visible=False'')'#$D#$A'  PixelsPerInch = 96'#$D#$A'  TextHeight = 13'#$D#$A'  object Edit1: TEdit'#$D#$A'    Left = 192'#$D#$A'    Top = 64'#$D#$A'    Width = 121'#$D#$A'    Height = 21'#$D#$A'    TabOrder = 8'#$D#$A'  end'#$D#$A'end'#$D#$A'''

afterwards.

The strange thing is that my complete command

'INSERT INTO designerFormDfm(designerFormDfmText) VALUES ("'object Form1: TScriptForm'#$D#$A'  Left = 0'#$D#$A'  Top = 0'#$D#$A'  Align = alClient'#$D#$A'  BorderStyle = bsNone'#$D#$A'  ClientHeight = 517'#$D#$A'  ClientWidth = 993'#$D#$A'  Color = clBtnFace'#$D#$A'  Font.Charset = DEFAULT_CHARSET'#$D#$A'  Font.Color = clWindowText'#$D#$A'  Font.Height = -11'#$D#$A'  Font.Name = ''MS Sans Serif'''#$D#$A'  Font.Style = []'#$D#$A'  OldCreateOrder = False'#$D#$A'  SaveProps.Strings = ('#$D#$A'    ''Visible=False'')'#$D#$A'  PixelsPerInch = 96'#$D#$A'  TextHeight = 13'#$D#$A'  object Edit1: TEdit'#$D#$A'    Left = 192'#$D#$A'    Top = 64'#$D#$A'    Width = 121'#$D#$A'    Height = 21'#$D#$A'    TabOrder = 8'#$D#$A'  end'#$D#$A'end'#$D#$A''");'

executes in a MySQL console, but not from Delphi, where I pass that command as parameter command to a function which

  ADOCommand.CommandText := command;
  ADOCommand.CommandType := cmdText;
  ADOCommand.Execute();

I can only assume that I am having problems escaping sequences which contain single quotes (and QuotedStr() doesn't seem to escape backslashes(?!))

What am I doing that is wrong?

Solution

  • @mawg, the @da-soft suggestion is ok , the best way to interact with inserts and updates is using parameters.

    check this sample 

    var
ADOCommand : TADOCommand;
begin
  ADOCommand:=TADOCommand.Create(nil);
  try
   ADOCommand.Connection:=AdoConnection;
   ADOCommand.Parameters.Clear;
   ADOCommand.CommandText:='INSERT INTO designerFormDfm (designerFormDfmText) VALUES (:designerFormDfmText)';
   ADOCommand.ParamCheck:=False;
   ADOCommand.Parameters.ParamByName('designerFormDfmText').Value:= YourData;
   ADOCommand.Execute;
  finally
  ADOCommand.Free;
  end;
end;