Generate a string like a WHERE statement by a dictionary

Question

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.

You're looking for Prepared Statements, let me look up a good example question from stackoverflow. — MatBailie, Mar 22 '11 at 14:54
You're not going to execute it? For what purposes are you attempting to sanitise it? — MatBailie, Mar 22 '11 at 15:07

score 0 · Answer 1 · edited May 23 '17 at 11:55

Try this link : Creating safe SQL statements as Strings

Some people consider this over-engineered, or just labourious to type. I fall back on a simple argument though...

Someone has already invested time and effort ensuring arguements can be safely and reliably included in SQL statements. Are you 100% certain you have pre-empted every possible scenario? Or is it more likely tried and tested code is more reliable?

But, then, I'm a bit anal ;)

score 0 · Answer 2 · answered Mar 22 '11 at 14:58

The first code is only a problem if name is something entered by the user. Otherwise, it should be fine.

I don't know that it eliminates all problems but you might try experimenting with something like name = name.Replace("'", "''"). By converting all single quotes to double single quotes, you prevent the type of problems you described. Another approach might be to remove any single quotes.

However, the best route is to use query arguments. ADO supports these nicely and that would also eliminate any possibility of injection attacks.

Strongly in favour of the second half of this suggestion. – MatBailie Mar 22 '11 at 15:02 — MatBailie, Mar 22 '11 at 15:02

score 0 · Answer 3 · answered Mar 22 '11 at 14:58

0

The easy way could be like this:

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

This of course wouldn't solve the quotes ' issue depending on different datatypes so you cannot use this as input to a SQL query - for that I would strongly recommend named parameters anyway - but is otherwise correct depending on the ToString() implementation of the values in your dictionary.

answered Mar 22 '11 at 14:58

BrokenGlass

158,293
28
286
335

One mention issue is dictionary entries including ' in the string. this will break the SQL code generated. (Or allow SQL Injection attacks if users can add to the dictionary.) Thus Prepared Statements... – MatBailie Mar 22 '11 at 15:01
@Dems as mentioned in my answer - this approach is not suitable for SQL queries - I wasn't suggesting this should be used that way, so only addresses the dictionary to string part. – BrokenGlass Mar 22 '11 at 15:05

Magnus · Answer 4 · 2011-03-22T15:14:48.727

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

score 0 · Answer 5 · answered Mar 22 '11 at 15:04

I wrote this many years ago, and always use it, and never ever have to think about this again. it is a waste of brain cells to solve this more than once:

        //   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;
    }

Use it like this:

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

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

Generate a string like a WHERE statement by a dictionary

5 Answers5

Linked