What is the safest way to include data in SQL WHERE clause while avoiding SQL injection risk WITHOUT using a parameterized query?

Question

What's a good function to use for making a string safe for inclusion in a SQL query? For example, apostrophes would need to be fixed, and there are doubtless other problems that could arise as well. I'd like a function that is rock-solid and would work given any possible input an evil-doer could devise.

Now, before the masses tell me to use query parameters and/or downvote/close this question, consider these points:

• I am stuck using a 3rd-party library with a poorly-designed API. The API is supposed to be called as follows:

  dataObjectVariable.FindWhere("WHERE RecordID = '(your string here)'");
  

  Now, I agree with you 100% that this is not a good API as it (1) exposes internal database field names to the user which are implementation details, (2) offers no opportunity for using parameters which would avoid this problem in the first place, (3) really, you could say that SQL itself is an implementation detail and shouldn't have been exposed. But I am stuck using this API because it's required to integrate with one of the leading systems in its industry. We're not really in a position to ask them to change their API, either.

• I searched this site for other questions pertaining to this issue, but found that answers tended to strongly suggest parameterized queries. Answers that tried to suggest writing a function to sanitize the string were often downvoted, not well thought out, etc. - I'm not sure if I trust them.

I'm only searching for strings and not other data types like numbers, dates, etc. Again, I'm 100% aware of the benefits of using parameterized queries and I wish I could use them, but I can't because my hands are tied on this one.

Answer 1

I have to use a similar API in one of our applications. Here's the validation routine I use to manually circumvent SQL injection:

internal class SqlInjectionValidator
{

    internal static readonly List _s_keywords = new List
    {
        "alter",
        "begin",
        "commit",
        "create",
        "delete",
        "drop",
        "exec",
        "execute",
        "grant",
        "insert",
        "kill",
        "load",
        "revoke",
        "rollback",
        "shutdown",
        "truncate",
        "update",
        "use",
        "sysobjects"
    };

    private string _sql;
    private int _pos;
    private readonly Stack _literalQuotes = new Stack();
    private readonly Stack _identifierQuotes = new Stack();
    private int _statementCount;

    // Returns true if s does not contain SQL keywords.
    public SqlValidationStatus Validate(string s)
    {
        if (String.IsNullOrEmpty(s))
        {
            return SqlValidationStatus.Ok;
        }

        _pos = 0;
        _sql = s.ToLower();
        _literalQuotes.Clear();
        _identifierQuotes.Clear();
        _statementCount = 0;

        List chars = new List();

        SqlValidationStatus svs;
        while (_pos = _sql.Length)
            {
                break;
            }

            if (_statementCount != 0)
            {
                return SqlValidationStatus.SqlBatchNotAllowed;
            }

            char c = _sql[_pos];
            if (IsEmbeddedQuote(c))
            {
                _pos++;
                chars.Add(_sql[_pos]);
                _pos++;
                continue;
            }

            if (c != '\'' &&
                    IsQuotedString())
            {
                chars.Add(c);
                _pos++;
                continue;
            }

            if (c != ']' &&
                    c != '[' &&
                    c != '"' &&
                    IsQuotedIdentifier())
            {
                chars.Add(c);
                _pos++;
                continue;
            }

            switch (c)
            {
                case '[':
                    if (_identifierQuotes.Count != 0)
                    {
                        return SqlValidationStatus.MismatchedIdentifierQuote;
                    }
                    svs = DisallowWord(chars);
                    if (svs != SqlValidationStatus.Ok)
                    {
                        return svs;
                    }
                    _identifierQuotes.Push(c);
                    break;

                case ']':
                    if (_identifierQuotes.Count != 1 ||
                            _identifierQuotes.Peek() != '[')
                    {
                        return SqlValidationStatus.MismatchedIdentifierQuote;
                    }
                    svs = DisallowWord(chars);
                    if (svs != SqlValidationStatus.Ok)
                    {
                        return svs;
                    }
                    _identifierQuotes.Pop();
                    break;

                case '"':
                    if (_identifierQuotes.Count == 0)
                    {
                        svs = DisallowWord(chars);
                        if (svs != SqlValidationStatus.Ok)
                        {
                            return svs;
                        }
                        _identifierQuotes.Push(c);
                    }
                    else if (_identifierQuotes.Count == 1)
                    {
                        svs = DisallowWord(chars);
                        if (svs != SqlValidationStatus.Ok)
                        {
                            return svs;
                        }
                        _identifierQuotes.Pop();
                    }
                    else
                    {
                        return SqlValidationStatus.MismatchedIdentifierQuote;
                    }
                    break;

                case '\'':
                    if (_literalQuotes.Count == 0)
                    {
                        svs = DisallowWord(chars);
                        if (svs != SqlValidationStatus.Ok)
                        {
                            return svs;
                        }
                        _literalQuotes.Push(c);
                    }
                    else if (_literalQuotes.Count == 1 &&
                            _literalQuotes.Peek() == c)
                    {
                        _literalQuotes.Pop();
                        chars.Clear();
                    }
                    else
                    {
                        return SqlValidationStatus.MismatchedLiteralQuote;
                    }
                    break;

                default:
                    if (Char.IsLetterOrDigit(c) ||
                            c == '-')
                    {
                        chars.Add(c);
                    }
                    else if (Char.IsWhiteSpace(c) ||
                            Char.IsControl(c) ||
                            Char.IsPunctuation(c))
                    {
                        svs = DisallowWord(chars);
                        if (svs != SqlValidationStatus.Ok)
                        {
                            return svs;
                        }
                        if (c == ';')
                        {
                            _statementCount++;
                        }
                    }
                    break;
            }

            _pos++;
        }

        if (_literalQuotes.Count != 0)
        {
            return SqlValidationStatus.MismatchedLiteralQuote;
        }

        if (_identifierQuotes.Count != 0)
        {
            return SqlValidationStatus.MismatchedIdentifierQuote;
        }

        if (chars.Count > 0)
        {
            svs = DisallowWord(chars);
            if (svs != SqlValidationStatus.Ok)
            {
                return svs;
            }
        }

        return SqlValidationStatus.Ok;
    }

    // Returns true if the string representation of the sequence of characters in
    // chars is a SQL keyword.
    private SqlValidationStatus DisallowWord(List chars)
    {
        if (chars.Count == 0)
        {
            return SqlValidationStatus.Ok;
        }

        string s = new String(chars.ToArray()).Trim();
        chars.Clear();

        return DisallowWord(s);
    }

    private SqlValidationStatus DisallowWord(string word)
    {
        if (word.Contains("--"))
        {
            return SqlValidationStatus.CommentNotAllowed;
        }
        if (_s_keywords.Contains(word))
        {
            return SqlValidationStatus.KeywordNotAllowed;
        }
        if (_statementCount > 0)
        {
            return SqlValidationStatus.SqlBatchNotAllowed;
        }
        if (word.Equals("go"))
        {
            _statementCount++;
        }

        return SqlValidationStatus.Ok;
    }

    private bool IsEmbeddedQuote(char curChar)
    {
        if (curChar != '\'' ||
                !IsQuotedString() ||
                IsQuotedIdentifier())
        {
            return false;
        }

        if (_literalQuotes.Peek() == curChar &&
                Peek() == curChar)
        {
            return true;
        }

        return false;
    }

    private bool IsQuotedString()
    {
        return _literalQuotes.Count > 0;
    }

    private bool IsQuotedIdentifier()
    {
        return _identifierQuotes.Count > 0;
    }

    private char Peek()
    {
        if (_pos + 1 < _sql.Length)
        {
            return _sql[_pos + 1];
        }

        return '\0';
    }

}