Recently I had a task to create MS SQL tables with dynamic names and my first thought was ok, I pass this as a parameter. But I was wrong, MS SQL (as of today) does not allow parameters as table names.
Note: One can argue that dynamic table names are bad programming practices, but let’s assume that we cannot go without it. The same principle applies for other dynamic queries. The purpose of this post is to demonstrate how to counter against SQL Injection if you have to use dynamic table names.
So, my option was string concatenation on the SQL statement. For example:
sql = $"SELECT [id], [category] FROM [Client_{clientName}] ORDER BY [id]";
The problem with the code above is SQL Injection attacks. If the variable clientName comes directly from the user, or anything that someone can control (for example configuration file), one can easily run unintended SQL command. These unintended statements can be simply to request hidden information or execute malicious code on the server.
Let’s take an example. Assume we have a table called Client_A with the following columns and data:
Id category 1 white 2 yellow 3 green 4 blue 5 red 6 black 7 magenta
Putting the above statement in a C# application will look like:
clientName = "A";
cmd.CommandText = $"SELECT [id], [category] FROM [Client_{clientName}]";
cmd.CommandType = System.Data.CommandType.Text;
cnx.Open();
using (var rdr = cmd.ExecuteReader())
{while (rdr.Read())
{Console.WriteLine($"id = {rdr.GetInt32(rdr.GetOrdinal("id"))},\tcategory = {rdr.GetString(rdr.GetOrdinal("category"))}");
}}
Once the code above is executed, it returns the following result:
Now, let’s assume that the value for variable clientName is changed to "A]; insert into [client_a] (id, category) values (7, 'magenta');". Now, once the code above is executed, it returns the same data but it also adds a new entry in the table. Thus:
SELECT * FROM [dbo].[Client_A];
returns:
Id category 1 white 2 yellow 3 green 4 blue 5 red 6 black 7 magenta
So, how can we prevent such attacks? The idea is to first check for the table if it exists and then perform our query. To check if the table exists we can do something like the code below.
SELECT 1 FROM [sys].[TABLES] WHERE [name] = @tableName
Where @tableName is an SQL parameter. If the code above returns 1, then we are certain that the table exists and there are no additional code at the end of our variable. If we get a value of 0, then we may want to log the event and trigger some warnings. The snippet below shows how this is done in C#.
private static bool IsTableNameValid(string tableName)
{var result = false;
using (var cnx = new SqlConnection(cnxString))
{using (var cmd = cnx.CreateCommand())
{cmd.CommandText = $"SELECT 1 FROM [sys].[tables] WHERE [name] = @tableName";
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add(new SqlParameter()
{ParameterName = "@tableName",
SqlDbType = System.Data.SqlDbType.VarChar,
Size = 100,
SqlValue = $"Client_{tableName}",
Direction = System.Data.ParameterDirection.Input
}) ;
cnx.Open();
int rc = 0;
var obj = cmd.ExecuteScalar();
if (obj != DBNull.Value && obj != null)
{rc = (int)obj;
}result = rc == 1;
}}return result;
}
You can browse and download the code used above from github.