I'm trying to replace parameters in a string to execute in an Npgsql query.
The problem is, when it replaces the parameter by its value in the string it adds unnecessary parentheses and so the query returns an error.
NAME_SCHEMA_DB
and NAME_ADMIN_DB
are string constants and
ExecuteCommand
just takes an NpgsqlCommand
and executes it.
This is my code:
String qdropSchema = @"DROP SCHEMA IF EXISTS @name_schem CASCADE";
String qCreateSchema = @"CREATE SCHEMA @name_schem AUTHORIZATION @name_admin";
DbCommand commandeDrop = new NpgsqlCommand(qdropSchema);
commandDrop.Parameters.Add(new NpgsqlParameter("@name_schem", NAME_SCHEMA_DB));
DbCommand commandCreate = new NpgsqlCommand(qCreateSchema);
commandCreate.Parameters.Add(new NpgsqlParameter("@name_schem", NAME_SCHEMA_DB));
commandCreate.Parameters.Add(new NpgsqlParameter("@name_admin", NAME_ADMIN_DB));
ExecuteCommand(commandDrop);
ExecuteCommand(commandCreate);
This is what the SQL query it tries to run when it reaches ExecuteCommand(commandDrop)
DROP SCHEMA IF EXISTS (('test_schemaName')) CASCADE;
I 'm not sure why it adds the extra parentheses and single quotes. Normally, I'd want the query it runs to be
DROP SCHEMA IF EXISTS test_schemaName CASCADE;
SQL parameters are generally only valid for values (e.g. the values of fields) - not field names and table names etc. While it's annoying, you'll probably need to embed these names directly into the SQL.
You should be very careful doing that, of course - anywhere that it might be from user input, you should use a whitelist of some form.
See more on this question at Stackoverflow