How can I safely create a query that will list column names given a table name, and a connection string which may point to SQL Server or MySQL?

I have the following method at the moment which works for my particular two databases, but I’m not sure if it’s the correct way to achieve my goal of listing column names, or is a bit hacky.

One part that worries me is that SQL Server seems to use TABLE_CATALOG for the database name and MySQL uses TABLE_SCHEMA. I’m not sure if I can count on this in every situation.

private string GetTableColumnsQuery(ConnectionStringSettings conString, string tableName)
{
    string query;
    if (conString.Name == "mysql")
    {
        var con = new MySqlConnectionStringBuilder(conString.ConnectionString);
        query = $"select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='{tableName}' and TABLE_SCHEMA='{con.Database}'";
    }
    else
    {
        var con = new SqlConnectionStringBuilder(conString.ConnectionString);
        query = $"select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='{tableName}' and TABLE_CATALOG='{con.Database}'";
    }

    return query;
}

Will this reliably work for all SQL Server and MySQL databases, and should I change it in a certain manner to avoid a bug that I’m missing?

Lastly, if the code is in no way user-facing, are prepared statements still encouraged?

Thanks.

Answer

Both MySQL and SQL Server have supported INFORMATION_SCHEMA views for a long time. So your code should work in either database. You are only selecting the column name so other differences in the tables won’t affect your code. The “schema” is interpreted a bit differently, but the code itself should work.

Yes, you should parameterize all queries that you are writing. First, it is a good habit — you wouldn’t want code that’s written for an internal purpose to be “extended”, introducing a SQL injection risk.

The other issues are re-use of the query plan and avoiding syntax errors. Munging a query string is not the best way to pass parameters.