I am using Entity Framework with PostgreSQL. I need to write a query that follows this format:
await Context.Database.ExecuteSqlCommandAsync("ALTER TABLE " + tableName + " ADD " + columnName + " " + sqlType);
As you can see, unfortunately I need to use user input for the table name, column name and the type of that column. And unfortunately, PostgreSQL doesn't support parameters for these types of input (like SQL Server does, for instance). So I don't really have an option here except to do string concatenation like so. I can, however, guarantee that both tableName and columnName will be constructed with the regex:
[a-zA-Z][a-zA-Z0-9]*
In other words I enforce that they will just be made up of just lowercase letters, uppercase letters and numbers. No punctuation or other special characters. As for sqlType, I can guarantee that this will be a string of the PostgreSQL type corresponding to the type for that column (e.g. "int4", "numeric", "uuid", etc). It will definitely not be anything else because I use a dictionary to map between .NET types and these types in string format.
So does anyone know if this is still at risk of SQL injection attacks? Is there anything else I can do?
You'd be safe with that regex (as it doesn't include any Unicode chars & punctuation).