CODEWITHSUNDEEP
sqldelphiparametersado
Jerry Dodge
Jerry Dodge

Reputation: 27276

How to use ADO Query Parameters to specify table and field names?

I'm executing an UPDATE statement in a TADOQuery and I'm using parameters for a few things. Initially, this was working just fine, but I added another parameter for the table name and field name, and now it's breaking.

The code looks like this:

Q.SQL.Text:= 'update :tablename set :fieldname = :newid where :fieldname = :oldid';
Q.Parameters.ParamValues['tablename']:= TableName;
Q.Parameters.ParamValues['fieldname']:= FieldName;
Q.Parameters.ParamValues['oldid']:= OldID;
Q.Parameters.ParamValues['newid']:= NewID;

And the error I get:

Error Message

I'm assuming this is because I'm using this field name twice. I can overcome this by using another unique field name for the second time it's used, however I still have another error:

Error Message

How do I use the parameters to specify the table and field to update?

Upvotes: 1

Views: 2696

Answers (1)

Ken White
Ken White

Reputation: 125689

Query parameters aren't designed to parameterize table names.

What you can do is use placeholders for the table name(s) in your SQL, and then use the Format function to replace those with the table name(s), and then use parameters for the other values as usual. This is still relatively safe from SQL injection (the malevolent person would have to know the precise table names, the specific SQL statement being used, and values to provide for parameters).

const
  QryText = 'update %s set :fieldname = :newid where :fieldname = :oldid';
begin
  Q.SQL.Text := Format(QryText, [TableName]);
  Q.Parameters.ParamValues['fieldname'] := FieldName;
  Q.Parameters.ParamValues['oldid'] := OldID;
  Q.Parameters.ParamValues['newid'] := NewID;    
  ...
end;

Upvotes: 5

Related Questions