How to execute complex SQL statements without parameters (stored procedures)

Data Abstract
1

From our client application we would like to send to application server some SQL instructions to create some stored procedures on database. Most of them are working fine but, sometimes, the code contains “:” character that is misinterpreted as parameter instead of script code.

Q: How can be avoid that misinterpretation and just execute without parameters check?

Our system short description:

  • Embarcadero Delphi 2010 environment;
  • RemObjects SDK 6.0.55.957 / DA (Zeos driver);
  • PostgreSQL.
2

You can execute raw SQL on server-side with IDAConnection.NewCommand :

// ic: IDASQLCommand 
ic := Connection.NewCommand(my_complex_sql, stSQL);
ic.Execute;

if you want to execute raw SQL from client-side, you can use TDataAbstractService.SQLExecuteCommand:

FService.SQLExecuteCommand(my_complex_sql);

Note: Allowing clients to execute arbitrary SQL statements poses a security risk and should only be allowed with caution and when using proper authentication mechanisms to prevent anonymous users from making such calls.

The AllowExecuteCommands property is set False by default, to prevent SQL commands from being executed.

3

That’s the way we executing raw SQL’s. On client side, we use TDARemoteDataAdapter.UpdateDataCall, having “SQLExecuteCommand” as method name:
…
RDA.UpdateDataCall.ParamByName(‘aSQLText’).AsString := some_SQL;
RDA.UpdateDataCall.Execute();
…

What we want to execute, a small piece of it:

CREATE OR REPLACE FUNCTION some_function(a numeric[], b numeric[]) RETURNS numeric[] AS
$BODY$
DECLARE
max numeric;
alpha numeric;
…
epsilon numeric := 1e-10; <--------------------------- Here!
…
BEGIN

…
SELECT ARRAY (SELECT unnest(A[p:p])) INTO tmp; <------------- Also here …
SELECT A[1:p-1] || A[max:max] || A[p+1:array_upper(A, 1)] into A; <------------- Also here …
SELECT A[1:max-1] || tmp || A[max+1:array_upper(A, 1)] into A; <------------- Also here …
…
RETURN x;
END;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;

The error is: Exception: : An exception was raised on the server: Incorrect token followed by “:”’.
If we run this script on Postgres from PgAdmin, it work fine.

4

You are right. standard implementation of SQLExecuteCommand calls RefreshParams for parsing parameters inside NewCommand:

  lsql:= Connection.NewCommand(lSQLText, stSQL);

You can create your own method based on SQLExecuteCommandEx and replace above line with

  lsql := Connection.NewDataset(lSQLText);

In this case, parameters won’t be parsed