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

Question

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:

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:

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

Answer 1

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;