When using CREATE SCHEMA schema_name in postgres, how do you prevent SQL injection of the schema_name parameter, if it needs to be user-input?
schema_name can't be a quoted value, so for example ActiveRecord::Base.sanitize() won't work. (if your using rails).
It's rather unusual (even scary) to allow users to create schemas (in general to do DDL). Are you sure you want to do that?
In any case, you can do the usual things: either sanitize you input (and I guess that if you allow the user to create schames at least you can restrict the allowed names - eg only alphanumeric, etc), and/or use a prepared statement, binding the schema name (this depends on your language/environment).
Users should never have DDL permissions, they don't need it and it gives them too much power.
You could use a SQL function using quote_ident, to create a schema and avoid SQL injection:
CREATE OR REPLACE FUNCTION new_schema(IN _schemaname TEXT)
RETURNS bool
LANGUAGE plpgsql
AS
$$
BEGIN;
EXECUTE 'CREATE SCHEMA ' || quote_ident(_schemaname);
RETURN true;
END;
$$
SECURITY DEFINER;
-- test:
SELECT new_schema('Frank; drop database template1');
I know this probably isn't the answer you are looking for, but if your design has users deciding your schema names, but you don't trust your users, you really need to do a re-design. Having your users decide on a schema name is akin to asking users to decide on class and function names.
If you really, really want to do this, I would use a regular expression to limit to something like 16 alphanumeric characters only, starting with an alphabetic character, after being forced lowercase (/^[a-z][a-z0-9]{1,15}$/). You will also need to explicitly filter out "public" as a schema name, information_schema (if you allow underscores as well), and other built-in schema names.
Again, I'll be willing to bet that you really don't want to do this, and that there's a better way to solve your underlying problem.
