Create database from user input with prepared statement to prevent SQL injection

Question

In our application, we are accepting user input (like project name) and then use it to create a database for the user (among other things). I would like to prevent SQL injection, but cannot prepare SQL statement for creating a database and granting access. Is there a safe way to prevent users from SQL injecting us? All I could think is limiting input to letters of English alphabet and spaces (and for DB name replace them with underscores), which in turn could provide protection if we enclose our SQL statements within single quotes. Is this a plausible solution?

We are using java 8/spring boot with a Postgres 10.6 database. I have played around with SQL and a prepared statement, to my understanding, can be only used for queries like update, delete and update. I have tried fiddling around the code to try to drop some tables from user input, but it, fortunately, didn't work, but I would like to be assured that the application isn't left vulnerable.

String createDbSQL = "create database ?";
Connection connection = DriverManager.getConnection(env.getDbUrl(), env.getDbUsername(), env.getDbPassword());
connection.setAutoCommit(false);
PreparedStatement preparedStatement = connection.prepareStatement(createDbSQL);
preparedStatement.setString(1, "test_db_name");
preparedStatement.execute();

fails with org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1" (which makes sense if prepared statement cannot be used for create database`

PREPARE foo (text) AS create alter database $1;

expects query expression and doesn't work

Nathan Hughes Nathan Hughes · Accepted Answer · 2019-05-10T21:26:31

This isn't necessary, and could cause problems beyond just the sql injection possibilities. If you're in charge of providing the database you need to be in charge of naming.

You can let the user provide a name, as far as they're concerned that's the database's name, but your code to create and use the database can use a name that you generate and control yourself. That way if for some reason you need to give it a different name, or create a new copy of the database, or switch to a different database platform where the user-provided name follows different rules (and maybe their chosen name is not valid), you aren't prevented from any of that.

Create database from user input with prepared statement to prevent SQL injection

3 Answers