4

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

Johnny
  • 87
  • 7

3 Answers3

2

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.

Nathan Hughes
  • 94,330
  • 19
  • 181
  • 276
  • This seems like a solution I am ok with. The user only inputs project name, and as you said, we should be in charge of naming the database, so we will restrict the name to characters that only make sense and name the database with underscore_lowercase, which shouldn't cause problems. Thank you – Johnny May 10 '19 at 21:48
1

Either you only allow a-zA-Z0-9, or you could use escapeLiteral

stena
  • 667
  • 5
  • 19
0

You are not allowed to create databases in the PREPARE syntax:

statement
Any SELECT, INSERT, UPDATE, DELETE, or VALUES statement.

(Postgresql 10 docs) (BTW: It’s 10.8 nowadays. Or 11.3)

So you will have to create the database in a normal statement.

The title of your question is unrelated to the question itself, as you have hardcoded the database name and so avoided any attacks. Most probably you want to deal with user provided database names. If that is the case, that part of your question is a duplicate.

Christian H. Kuhn
  • 343
  • 2
  • 16
  • I know there are more up to date versions, but there are certain reasons because of which we can't upgrade. The "hardcoded" part is only to show the code that I have tried - ofc the db name is an input. Most of the answers link to parameters in prepared statements, and as you have said, I can't use those. I will most likely have to restrict the user input (they do not choose the db name, they just choose project name based on which we generate the name) – Johnny May 10 '19 at 21:44