AdoQuery not working with SHOW: command

and I am tearing my hair out!!

Even something simple like this work:

procedure MyAdoQueryTest();
   const MYSQL_CONNECT_STRING='Driver={MySQL ODBC 5.1 Driver};Server=%s;Port=3306;Database=%s;User=%s;Password=%s;Option=3;';

   var   AdoConnection  : TADOConnection;
         ADOQuery : TADOQuery;
         Param    : TParameter;
begin
   AdoConnection := TADOConnection.Create(Nil);
   AdoConnection.ConnectionString := Format(MYSQL_CONNECT_STRING,['localhost',
                                                                  'mysql',
                                                              'root',
                                                              '']);
    AdoConnection.LoginPrompt := False;
    AdoConnection.Connected := True;

   ADOQuery := TADOQuery.Create(Nil);
   ADOQuery.Connection := AdoConnection;
   ADOQuery.Sql.Clear();
   ADOQuery.SQl.Add('SHOW :what_to_show');

   Param := ADOQuery.Parameters.ParamByName('what_to_show');
   Param.DataType := ftString;
   Param.Value := 'databases';

   ADOQuery.Prepared := true;
   ADOQuery.Active := True;
end;

(btw, do I really need to use the 'Param' variable and 3 statements, or can I just ` ADOQuery.Parameters.ParamByName('what_to_show').Value := 'databases';?)

Anyway, when I run it, I get an exception at ADOQuery.SQl.Add('SHOW :what_to_show'); which says "Arguments are of the wrong type, are out of the acceptable range or are in conflict with one another".

What I am trying to do is to make 2 central functions: one which will accept and execute any SQL statement which will not re开发者_StackOverflow中文版turn any data (such as INSERT INTO) and oen which will (such as SELECT).

I currently have these working with AdoConnection only, but am now trying to use AdoQuery because I want to parametrize my SQL statements to handle strings with quotes in them.

I can has halpz?

---

The error is here:

ADOQuery.SQl.Add('SHOW :what_to_show');

The :Param can only be used for values, not for dynamic column/keyword/table/database names.
This is because if it worked like that you'd have an SQL-injection risk depending on the contents of your parameter.

In order to fix that you'll have to inject your what_to_show thingy into the SQL-string.

Like so:

var
  what_to_show: string;
begin
  ....
  what_to_show:= 'tables';
  ADOQuery.SQL.Text:= ('SHOW '+what_to_show);
  ....

Now it will work.

Warning
Make sure test everything you inject into the SQL to prevent users from being able inject their SQL-code into your queries.
Parameters prevent SQL injection, but because you cannot use them here you need to check them against a list of pre-approved values. e.g. a stringlist holding all the allowed what_to_shows.
Escaping or use of special chars is useless.

Safe injection example code

var
  what_to_show: string;
  i: integer;
  inputapproved: boolean;
begin
  ....
  what_to_show:= lower(trim(someinput));
  i:= 0;
  inputapproved:= false;
  while (i < WhiteList.count) and not(inputapproved) do begin
    inputapproved:= ( what_to_show = lower(Whitelist[i]) );
    Inc(i);
  end; {while}
  if inputapproved then ADOQuery.SQL.Text:= ('SHOW '+what_to_show);
  ....