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.
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.
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)
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.