Why Delphi adds these characters #$D#$A at the end of line?

[mischmasch]

Hello,

I'm traying to Update my table by using SQL command but all the time occures some error.

My code is simple:

with ADOQuery_PasswordChange do
begin
Connection:=ufMain.ADOConnection1;
SQL.Clear;
SQL.Text:='UPDATE tblAccount SET Password=''' + 'XXX' + ''' WHERE UserID=''' + 'AAABBB' + ''';';
ExecSQL;
end;

Delphi, I don't know how and why, adds some additional characters #$D#$A to my SQL.Text.

Instead of this

UPDATE tblAccount SET Password='XXX' WHERE UserID='AAABBB';

there is

UPDATE tblAccount SET Password='XXX' WHERE UserID='AAABBB';#$D#$A

and when ExecSQL is executed, I receive error.


I've tried to use Trim function but unsuccessfully.


Anyone knows how to solve this problem?
In Unit I have many similar commands and only at the end of this line Delphi adds this characters, why? I don't see anything special in this line.

 

[whosrdaddy]

#$D#$A is just carriage return and linefeed.
you get these chars when reading the ADOQuery_PasswordChange.SQL.Text property I presume?
nothing is wrong with that, it is just the way the TStringList object works. These chars are not the cause of your failing query.

One big red flag though is that you are constructing a query that is susceptible to SQL injection.
To avoid this you must use parameters:

procedure ChangePassword(UserId, NewPassword: String;)

var Query : TADOQuery;

begin
 Query := TADOQuery.Create(nil);
 with Query do begin
  Connection:=ufMain.ADOConnection1;
  ParamCheck := True; // must be true to force parameter creation
  SQL.Text := 'UPDATE tblAccount SET Password=:Password WHERE UserId=:UserId';
  Parameters.ParamByName('Password').Value := NewPassword; 
  Parameters.ParamByName('UserId).Value := UserId;
  ExecSQL;
 end;
end;

as a bonus parameters make your code more readable (no messing with quotes)...

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[whosrdaddy]

forgot a part:

procedure ChangePassword(UserId, NewPassword: String;)
var Query : TADOQuery;
begin 
 Query := TADOQuery.Create(nil); 
 with Query do try
  Connection:=ufMain.ADOConnection1;  
  ParamCheck := True; // must be true to force parameter creation  
  SQL.Text := 'UPDATE tblAccount SET Password=:Password WHERE UserId=:UserId';  
  Parameters.ParamByName('Password').Value := NewPassword;     
  Parameters.ParamByName('UserId).Value := UserId;  
  ExecSQL; 
 finally
   FreeAndNil(Query);
 end;
end;

as you can see you can create adoquery objects on the fly, no need to drop one on a form...
If you don't want to cope with the object cleanup, I wrote a handy little interface that wraps the ADOQuery object:

unit u_db_utilities;

interface

uses
  Windows,
  SysUtils,
  Classes,
  Controls,
  ADODB,
  DB;

const ADOConnStrTemplate = 'Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=%s;Data Source=%s;User ID=%s;Password=%s';
      MySQL5StrTemplate = 'Provider=MSDASQL.1;Persist Security Info=True;User ID=%0:s;Password=%1:s;Extended Properties="DRIVER={MySQL ODBC 5.1 Driver};SERVER=%2:s;PORT=3306;DATABASE=%3:s;USER=%0:s;PASSWORD=%1:s;OPTION=1048579"';
      MySQL3StrTemplate = 'Provider=MSDASQL.1;Persist Security Info=True;User ID=%0:s;Password=%1:s;Extended Properties="DRIVER={MySQL ODBC 3.51 Driver};SERVER=%2:s;PORT=3306;DATABASE=%3:s;USER=%0:s;PASSWORD=%1:s;OPTION=1048579"';

type
  TDBtype = (dbMsSQL, dbMySQL3, dbMySQL5);

const
  TDBTypeStrings : array[dbMsSQL..dbMySQL5] of string[10] = ('MsSQL', 'MySQL3', 'MySQL5');

type
  TDateTimeType = (dtDate, dtTime, dtDateTime);

  IIADOQuery = interface
  ['{344B711B-FBB2-473B-A864-F5D0ABB7A8F8}']
    procedure ParseQuery(SQL : String);
    procedure SelectParsedQuery;
    procedure UpdateParsedQuery;
    procedure SelectQuery(SQL : String);
    procedure UpdateQuery(SQL : string);
    procedure SelectQueryEx(SQL : String; var Flag : Boolean);
    procedure UpdateQueryEx(SQL : string; var Flag : Boolean);
    procedure UpdateParsedQueryEx(var Flag : Boolean);
    procedure SelectParsedQueryEx(var Flag : Boolean);
    procedure Parameter(ParameterName : String; Value : String); overload;
    procedure Parameter(ParameterName : String; Value : Integer); overload;
    procedure Parameter(ParameterName : String; Value : Boolean); overload;
    procedure Parameter(ParameterName : String; Value : Real); overload;
    procedure Parameter(ParameterName : String; Value : String; CurrencySymbol: String); overload;
    procedure Parameter(ParameterName : String; Value : String; DateTimeType : TDateTimeType); overload;
    procedure Parameter(FieldType : TFieldType; ParameterName : String; Value : Variant); overload;
    function AsString(FieldName : String) : String;
    function AsInteger(FieldName : String) : Integer;
    function AsFloat(FieldName : String) : Double;
    function AsDateTime(Fieldname : String) : TDateTime;
    function EOF : Boolean;
    function RecordCount : Integer;
    procedure Next;
    procedure Previous;
    procedure Edit;
    procedure Post;
    function Qry : TADOQuery;
  end;

  TIADOQuery = class(TInterfacedObject, IIADOQuery)
  private
  public
    ADOQuery : TADOQuery;
    procedure ParseQuery(SQL : String);
    procedure SelectParsedQuery;
    procedure UpdateParsedQuery;
    procedure SelectQuery(SQL : String);
    procedure UpdateQuery(SQL : string);
    procedure SelectQueryEx(SQL : String; var Flag : Boolean);
    procedure UpdateQueryEx(SQL : string; var Flag : Boolean);
    procedure UpdateParsedQueryEx(var Flag : Boolean);
    procedure SelectParsedQueryEx(var Flag : Boolean);
    procedure Parameter(ParameterName : String; Value : String); overload;
    procedure Parameter(ParameterName : String; Value : Integer); overload;
    procedure Parameter(ParameterName : String; Value : Boolean); overload;
    procedure Parameter(ParameterName : String; Value : Real); overload;
    procedure Parameter(ParameterName : String; Value : String; CurrencySymbol: String); overload;
    procedure Parameter(ParameterName : String; Value : String; DateTimeType : TDateTimeType); overload;
    procedure Parameter(ParameterName: String; Value: TDate); overload;
    procedure Parameter(FieldType : TFieldType; ParameterName : String; Value : Variant); overload;
    function AsString(FieldName : String) : String;
    function AsInteger(FieldName : String) : Integer;
    function AsFloat(FieldName : String) : Double;
    function AsDateTime(Fieldname : String) : TDateTime;
    function EOF : Boolean;
    function RecordCount : Integer;
    procedure Next;
    procedure Previous;
    procedure Edit;
    procedure Post;
    function Qry : TADOQuery;
    constructor Create; overload;
    constructor Create(ADOConnection : TADOConnection); overload;
    constructor Create(ADOConnection: TADOConnection; cDynamic: Boolean);overload;
    destructor Destroy; override;
  end;

function CreateADOConnection(DBType : TDBType; SQLServer, SQLUsername, SQLPassword, Catalog : String) : TADOConnection;
function CreateConnectionString(DBType : TDBType; SQLServer, SQLUsername, SQLPassword, Catalog : String) : String;
function StringToDBType(db : string) : TDBtype;

implementation

// general purpose routines
function StringToDBType(db : string) : TDBtype;

var dbtype : TDBtype;

begin
 for dbtype := Low(TDBTypeStrings) to High(TDBTypeStrings) do
  begin
   if AnsiSameText(TDBTypeStrings[dbtype], db) then
    begin
     Result := dbtype;
     Exit;
    end;
  end;
 raise Exception.CreateFmt('Not database type found for "%s"', [db]); 
end;

function CreateConnectionString(DBType : TDBType; SQLServer, SQLUsername, SQLPassword, Catalog : String) : String;

begin
 case DBType of
  dbMsSQL: Result := Format(ADOConnStrTemplate,[Catalog, SQLServer, SQLUsername, SQLPassword]);
  dbMySQL3: Result := Format(MySQL3StrTemplate,[SQLUsername, SQLPassword, SQLServer, Catalog]);
  dbMySQL5: Result := Format(MySQL5StrTemplate,[SQLUsername, SQLPassword, SQLServer, Catalog]);
 end;
end;

function CreateADOConnection(DBType : TDBType; SQLServer, SQLUsername, SQLPassword, Catalog : String) : TADOConnection;
begin
 Result := TADOConnection.Create(nil);
 Result.ConnectionString := CreateConnectionString(DBType, SQLServer, SQLUsername, SQLPassword, Catalog);
end;


{ TInterfacedADO }

constructor TIADOQuery.Create;
begin
 if not Assigned(ADOQuery) then
  ADOQuery := TADOQuery.Create(nil);
 ADOQuery.ParamCheck := False;
end;

constructor TIADOQuery.Create(ADOConnection : TADOConnection);
begin
 Create;
 ADOQuery.Connection := ADOConnection;
end;

constructor TIADOQuery.Create(ADOConnection: TADOConnection; cDynamic: Boolean);
begin
 Create(ADOConnection);
 if cDynamic then
  begin
   ADOQuery.CursorLocation := clUseServer;
   ADOQuery.CursorType := ctDynamic;
 end;
end;

destructor TIADOQuery.Destroy;
begin
 FreeAndNil(ADOQuery);
 inherited;
end;

procedure TIADOQuery.ParseQuery(SQL: String);
begin
 ADOQuery.ParamCheck := True;
 ADOQuery.SQL.Text := SQL;
end;

function TIADOQuery.Qry: TADOQuery;
begin
 Result := ADOQuery;
end;

function TIADOQuery.RecordCount: Integer;
begin
 Result := ADOQuery.RecordCount;
end;

procedure TIADOQuery.SelectParsedQuery;
begin
 ADOQuery.Active := False;
 ADOQuery.Active := True;
end;

procedure TIADOQuery.SelectQuery(SQL: String);
begin
 ADOQuery.Active := False;
 ADOQuery.ParamCheck := False;
 ADOQuery.SQL.Text := SQL;
 ADOQuery.Active := True;
end;

procedure TIADOQuery.SelectQueryEx(SQL: String; var Flag: Boolean);
begin
 try
  SelectQuery(SQL);
 except
  Flag := False;
  raise;
 end;
end;

procedure TIADOQuery.UpdateParsedQuery;
begin
 ADOQuery.Active := False;
 ADOQuery.ExecSQL;
end;

procedure TIADOQuery.SelectParsedQueryEx(var Flag: Boolean);
begin
 try
  SelectParsedQuery;
 except
  Flag := False;
  raise;
 end;
end;

procedure TIADOQuery.UpdateParsedQueryEx(var Flag : Boolean);
begin
 try
  UpdateParsedQuery;
 except
  Flag := False;
  raise;
 end;
end;

procedure TIADOQuery.UpdateQuery(SQL: string);
begin
 ADOQuery.Active := False;
 ADOQuery.ParamCheck := False;
 ADOQuery.SQL.Text := SQL;
 ADOQuery.ExecSQL;
end;

procedure TIADOQuery.UpdateQueryEx(SQL: string; var Flag: Boolean);
begin
 try
  UpdateQuery(SQL);
 except
  Flag := False;
  raise;
 end;
end;

procedure TIADOQuery.Parameter(ParameterName, Value: String);
begin
 ADOQuery.Parameters.ParamByName(ParameterName).Value := Value;
 ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftString;
end;

procedure TIADOQuery.Parameter(ParameterName: String; Value: Integer);
begin
 ADOQuery.Parameters.ParamByName(ParameterName).Value := Value;
 ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftInteger;
end;

procedure TIADOQuery.Parameter(ParameterName: String; Value: Boolean);
begin
 ADOQuery.Parameters.ParamByName(ParameterName).Value := Value;
 ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftBoolean;
end;

procedure TIADOQuery.Parameter(ParameterName: String; Value: Real);
begin
 ADOQuery.Parameters.ParamByName(ParameterName).Value := Value;
 ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftFloat;
end;

procedure TIADOQuery.Parameter(ParameterName: String; Value: TDate);
begin
 ADOQuery.Parameters.ParamByName(ParameterName).Value := Value;
 ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftDate;
end;

procedure TIADOQuery.Parameter(FieldType: TFieldType; ParameterName: String; Value: Variant);
begin
 ADOQuery.Parameters.ParamByName(ParameterName).Value := Value;
 ADOQuery.Parameters.ParamByName(ParameterName).DataType := FieldType;
end;

procedure TIADOQuery.Parameter(ParameterName, Value, CurrencySymbol: String);

var ConvertedValue : Real;
    Ps : Integer;

begin
 Ps := Pos(CurrencySymbol, Value);
 if Ps > 0 then
  Value := Copy(Value, 1, Ps - 1);
 ConvertedValue := StrToFloat(Value);
 ADOQuery.Parameters.ParamByName(ParameterName).Value := ConvertedValue;
 ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftFloat;
end;

procedure TIADOQuery.Parameter(ParameterName, Value: String; DateTimeType: TDateTimeType);
begin
 case DateTimeType of
  dtDate : 
 end;
end;

function TIADOQuery.AsDateTime(Fieldname: String): TDateTime;
begin
 Result := ADOQuery.FieldByName(FieldName).AsDateTime;
end;

function TIADOQuery.AsFloat(FieldName: String): Double;
begin
 Result := ADOQuery.FieldByName(FieldName).AsFloat;
end;

function TIADOQuery.AsInteger(FieldName: String): Integer;
begin
 Result := ADOQuery.FieldByName(FieldName).AsInteger;
end;

function TIADOQuery.AsString(FieldName: String): String;
begin
 Result := ADOQuery.FieldByName(FieldName).AsString;
end;

procedure TIADOQuery.Edit;
begin
 Qry.Edit;
end;

function TIADOQuery.EOF: Boolean;
begin
 Result := Qry.Eof;
end;

procedure TIADOQuery.Next;
begin
 Qry.Next;
end;

procedure TIADOQuery.Post;
begin
 Qry.Post;
end;

procedure TIADOQuery.Previous;
begin
 Qry.Prior;
end;

end.

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[whosrdaddy]

rewriting your query with my interface:

procedure ChangePassword(UserId, NewPassword: String;)

var Query : IIADOQuery;

begin
 Query := TIADOQuery.Create(ufMain.ADOConnection1);
 Query.ParseSQL('UPDATE tblAccount SET Password=:Password WHERE UserId=:UserId');
 Query.Parameter('UserId', UserId);
 Query.Parameter('Password', NewPassword);
 Query.UpdateParsedQuery;
end;

since query is an interface it will free itself when the variable is no longer referenced.

Cheers,
Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[mischmasch]

It works! Thank you

 

[djjd47130]

Of course it works, Daddy made it


JD Solutions