Geek asks and answers

Generate string as WHERE statement using dictionary

I know that 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

due to SQL injection, because the name may contain char 'due to another 100 reasons. But now I have to do something similar. I have Dictionary<string, object>, the data of which is as follows:

   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 the elements in a dictionary assembled in String, 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 using ', but note that the name may be O'Neal. Is there a simple implementation? Enter the dictionary and return the string as a result.

, , , , . , WHERE.

+1
5

: SQL

. , ...

- , , SQL. 100% , ? , , ?

, , ;)

0

- , name - -, . .

, , - name = name.Replace("'", "''"). , . .

- . ADO , .

0

:

string results = string.Join(" and ", myDict.Select( x=> x.Key + " = " +  x.Value));

, , ' , SQL- - , ToString() .

0
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("'", "''") + "'");
}

, ,

 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);
        }
0

. , :

        //   replace things like:
    //         O'Keefe     with
    //          'O''Keefe'
    //   make sure you don't call this twice!
    static public string SqlString(string strInputSQL)
    {
        string strOut;
        strOut = strInputSQL;
        strOut = strOut.Replace ("'", "''");
        strOut = "'" + strOut + "'";
        return strOut;
    }

:

string sql = "SELECT * FROM FOO WHERE Name LIKE" + SqlString (myvalue);

There may be a dozen other ways to do this, but if you can have one and only one function and use it sequentially, you will save a lot of time.

0
source

More articles:


All Articles