PostgreSQL PreparedStatement help : API1

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

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

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 
Or
CREATE ROLE "Training"

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

Thanks,
-Karen

Turns out you can’t use prepared statements for things like CREATE USER

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))
RETURNS OID
LANGUAGE plpgsql
AS $NewUser$
BEGIN 
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);
END;
$NewUser$;

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.

-Karen

I see you’ve come up with a work around, but I can’t see the bindpoint type definition in your code (PS.bindtype) and wonder if that’s the issue?

Thanks Wayne for the suggestion.

The Xojo Docs said you don’t need to define Bind type for Postgres unless it is for a bytea field.

But in any case that is definitely not the issue here. I also posted the question elsewhere and got quick responses from Dave S. and Norm.

What Norm said was:

Once I realized what I was trying to do was not possible, and what to search for, I found the answer… (I had been seeing the terms DDL and DML in on line searches, but had no idea what they meant!)

-Karen