I'm working on an application using JDBC and MySQL, and I'm worried about SQL Injection. The database will be multi-tenant, with a different "schema" for each tenant. The application, as a result, needs the ability to create and drop schemas.
My challenge is that those kinds of queries do not seem to work with PreparedStatements, I can't parameterize them. This leaves me a bit scared, I don't like concatenating Strings with SQL commands.
My only consolation is that the application users cannot create the schema names directly. They're composed of the first five characters of the tenant organization's name, plus a unique index. That is at least some consolation.
I am however looking for best practices on what, if anything, I can do to secure the following SQL statements from potential injection attacks (The parameters obviously are just placeholders for this question, these statements can't be parameterized):
CREATE SCHEMA ?
SHOW TABLES FROM ? (used to verify that the schema DDL script was executed properly)
DROP SCHEMA IF EXISTS ?
If the only string you're "injecting" by using string concatenation is entirely under your control, you'll be fine.
However, even being under your control, you can still guard yourself by either rejecting or escaping values with special characters in them (the cause of SQL injection issues). Assuming you're quoting your string literals using
', the following two special characters need consideration:
If you properly escape strings you "inject" into SQL, then even user-supplied strings are ok to use. It is the lack of escaping strings that causes SQL Injection vulnerabilities.
Note that the above is only guaranteed to be true because you're using MySQL. Other RDBMS's may use other special characters in a
'-quoted string literal, which is why using
PreparedStatement markers is generally the best way to insure against SQL Injection attacks, where the JDBC driver can do the correct escaping where needed.