我知道我们不应该这样做:
字符串选择=SELECT * from table1中;
字符串,其中=其中name ='+姓名+';
字符串SQL =选择+哪里;
//执行通过ADO.NET的SQL
由于SQL注入,因为名称可以包含因为另一个100个理由焦炭。但现在我的必须做类似的东西。我有一个
词典<字符串,对象>
的数据是这样的:
键(串)值(对象)
姓名,鲍勃//字符串
ID10092L //长
生日1980年5月7日00:00:00 //日期时间
工资5000.5米//十进制
//一些人,它的键是一个字符串,而值是字符串/长/ INT /日期/十进制
我想要一个简单的方法,来获得在收集在字符串
词典中的所有项目,就像一个WHERE语句:
名称='鲍勃'和ID = 10092和生日='1980年5月7日00:00:00和工资= 5000.5
字符串和日期都没有报价,回收,但请注意,该名称可以是
奥尼尔
。有没有简单的实现?输入字典,并返回字符串作为结果。
修改请注意,我要的是字符串,我不会去执行它,参数化的命令并没有帮助。我只想要一个字符串,它看起来像一个完美的安全WHERE语句。
解决方案 VAR SB =新的StringBuilder();
VAR isFirst = TRUE;
的foreach(在DIC VAR元)
{
如果(!isFirst)
sb.Append(与);
其他
isFirst = FALSE;
sb.Append(element.Key);
sb.Append(=);
如果(element.Value为十进制)
sb.Append(CastToSqlDecimalString((十进制)element.Value));
其他
sb.Append('+的String.Format(CultureInfo.InvariantCulture,{0:绿},element.Value).Replace(','')+');
}
您可能要处理使用此功能小数
公共静态字符串CastToSqlDecimalString(十进制DEC)
{
VAR SQLDECIMAL =新System.Data.SqlTypes.SqlDecimal(DEC);
返回的String.Format(CAST({0}为十进制({1},{2})),
的String.Format(System.Globalization.CultureInfo.InvariantCulture,{0:绿},DEC)
SQLDECIMAL。precision,
sqlDecimal.Scale);
}
I know we should never do this:
string select = "SELECT * FROM table1 ";
string where = "WHERE Name = '" + name + "' ";
string sql = select + where;
//execute the sql via ADO.NET
because of sql injection, because name can contain the char '
, because of another 100 reasons. But now I have to do something similiar. I have a Dictionary<string, object>
whose data look like:
Key(string) Value(object)
"Name" "Bob" //string
"ID" 10092L //long
"Birthday" 1980-05-07 00:00:00 //DateTime
"Salary" 5000.5m //decimal
//some others, whose key is a string, and value is string/long/int/DateTime/decimal
I want an easy way, to get all items in the dictionary collected in a String
, just like a where statement:
Name = 'Bob' and ID = 10092 and Birthday = '1980-05-07 00:00:00' and Salary = 5000.5
String and DateTime are quoted with '
, but note that the Name can be O'Neal
. Is there any easy implementation? Input the dictionary, and return the string as a result.
EDIT Note that what I want is the string, I'm not going to execute it, parameterized command doesn't help. I just want a string that looks like a perfect safe WHERE statement.
解决方案var sb = new StringBuilder();
var isFirst = true;
foreach (var element in dic)
{
if(!isFirst)
sb.Append(" AND ");
else
isFirst = false;
sb.Append(element.Key);
sb.Append(" = ");
if(element.Value is decimal)
sb.Append(CastToSqlDecimalString((decimal)element.Value));
else
sb.Append("'" + String.Format(CultureInfo.InvariantCulture, "{0:G}", element.Value).Replace("'", "''") + "'");
}
You might want to handle decimals using this function
public static string CastToSqlDecimalString(decimal dec)
{
var sqlDecimal = new System.Data.SqlTypes.SqlDecimal(dec);
return string.Format("CAST({0} AS DECIMAL({1}, {2}))",
string.Format(System.Globalization.CultureInfo.InvariantCulture, "{0:G}", dec),
sqlDecimal.Precision,
sqlDecimal.Scale);
}