Preventing SQL Injections with string based queries
I'm working with an SDK to execute queries in a mssql database. This SDK offers a method that is required to execute those queries, but this method only takes a string. And string based queries are damn prone to sql injection attacks. So I want to write a middle piece method that takes in a SqlCommand object in which I can set parameters and returns the assembled query as string. Please give me feedback on it:
public static string GetParsedSqlCommand(SqlCommand cmd)
{
string sql = cmd.CommandText;
foreach (SqlParameter param in cmd.Parameters)
{
string placeholder = param.ParameterName;
string value = FormatSqlValue(param);
sql = sql.Replace("@" + placeholder, value);
}
return sql;
}
public static string FormatSqlValue(SqlParameter param)
{
if (param.Value == DBNull.Value || param.Value == null)
{
return "NULL";
}
switch (param.SqlDbType)
{
case System.Data.SqlDbType.NVarChar:
case System.Data.SqlDbType.VarChar:
case System.Data.SqlDbType.Char:
case System.Data.SqlDbType.NChar:
case System.Data.SqlDbType.Text:
case System.Data.SqlDbType.NText:
case System.Data.SqlDbType.UniqueIdentifier:
case System.Data.SqlDbType.Xml:
return $"'{param.Value.ToString().Replace("'", "''")}'";
case System.Data.SqlDbType.Date:
case System.Data.SqlDbType.DateTime:
case System.Data.SqlDbType.DateTime2:
case System.Data.SqlDbType.SmallDateTime:
case System.Data.SqlDbType.Time:
return $"'{((DateTime)param.Value).ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture)}'";
case System.Data.SqlDbType.Bit:
return ((bool)param.Value) ? "1" : "0";
default:
return param.Value.ToString();
}
}public static string GetParsedSqlCommand(SqlCommand cmd)
{
string sql = cmd.CommandText;
foreach (SqlParameter param in cmd.Parameters)
{
string placeholder = param.ParameterName;
string value = FormatSqlValue(param);
sql = sql.Replace("@" + placeholder, value);
}
return sql;
}
public static string FormatSqlValue(SqlParameter param)
{
if (param.Value == DBNull.Value || param.Value == null)
{
return "NULL";
}
switch (param.SqlDbType)
{
case System.Data.SqlDbType.NVarChar:
case System.Data.SqlDbType.VarChar:
case System.Data.SqlDbType.Char:
case System.Data.SqlDbType.NChar:
case System.Data.SqlDbType.Text:
case System.Data.SqlDbType.NText:
case System.Data.SqlDbType.UniqueIdentifier:
case System.Data.SqlDbType.Xml:
return $"'{param.Value.ToString().Replace("'", "''")}'";
case System.Data.SqlDbType.Date:
case System.Data.SqlDbType.DateTime:
case System.Data.SqlDbType.DateTime2:
case System.Data.SqlDbType.SmallDateTime:
case System.Data.SqlDbType.Time:
return $"'{((DateTime)param.Value).ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture)}'";
case System.Data.SqlDbType.Bit:
return ((bool)param.Value) ? "1" : "0";
default:
return param.Value.ToString();
}
}
