PostgreSQL PreparedStatement help : API1

In this code I keep getting an SQL error on SQL Execute near $1:

Dim PS as PostgreSQLPreparedStatement = DB.Prepare("CREATE ROLE $1")
PS.Bind(0, "Training")

But I can’t see what’s wrong. Anybody have a clue?

(Why that does not look like regular API 1 code is because I subclassed PostgresqlDatabase to throw exceptions on DB errors - but I get the same thing if use the Supeclass directly)

BTW What I was trying to check is how Xojo handled strings in this case for PG IDs. Would it be equivalent to:

CREATE ROLE "Training"

In other words if the role name wound up being as typed or all lowercase.


normally you cannot supply TABLE names in a PS… usually only values for field

The prepare statement is persistent execution plan - and execution plan
 contains pined source of data - so tables, column names cannot be 
mutable there.

certain statements cannot be used with prepareds

basically any DML (data manipulation language) can be (insert select update delete)
but DDL (data definition language) cant be (create table, create role, create index etc etc)

thats true for many db’s

The main reason I wanted to use a prepared statement was to avoid the possibility of injection… But I found a sort of workaround on-line.

I put it in stored function but I think I could create won “prepared statements” by doing the same thing using teh format function

Here is the stored function:

CREATE OR REPLACE FUNCTION NewUser(username varchar(63), thepassword  varchar(63))
LANGUAGE plpgsql
AS $NewUser$
EXECUTE format('CREATE USER  %I  IN ROLE user_basic CONNECTION LIMIT 1 PASSWORD %L', username, thepassword);
EXECUTE format('GRANT CONNECT On DATABASE the_db To %I', username);
RETURN (SELECT OID FROM pg_roles WHERE rolname = username);

Then I call it as:
Dim RS as RecordSet = DB.SQLSelect("Select NewUser( 'Training', 'test')")

%I means that string is formatted as a Postgres Identifier and %L means it is a quoted string literal.

So that is where the injection prevention comes from.

Anyway so now I know how to do this DDL stuff.