Validating ---> ' <--- (single quote) exception in c# (preventing SQL Injection)

Question

Hi I would like to know how you can stop simple SQL injection by catching the `

"Unclosed quotation mark" ---> ' <--- error

` when users enter bogus data onto a text box in ASP.NET C# I would like to know if there is a regular Expression that can be modified to catch all characters except Alphabets as I require to validate a search box which only takes in Alphabets however

if a person enters a ---> ' <--- quote mark like this

The whole program crashes with a server side error

If anyone knows a simple expression that can catch this it would be great

Thanks

Answer 1

I'm going to repost AbZy's original comment:

Use parametrized queries

You responded with:

sorry I can't help it as I'm working on a complex piece of code which i can do so much to make changes, that's why i'm asking if there's any other way to catch the exception than going through every single bit changing the queries all over again which would take days :(

That's not a good enough reason to avoid doing the work, unless this is an application which you're planning to throw away soon.

If you (or the original authors) write lots of bad code which is vulnerable to SQL injection attack, it will indeed take you a while to fix properly. That doesn't mean you should just try to patch it by detecting "bad" input. Sooner or later someone's going to need to include an apostrophe (e.g. to include a name such as "O'Neill") - at which point you'll have to do more work. At that point you might say "Well I won't detect the apostrophe - I'll escape it" - which will take you a while to do "mostly correctly" and you'll still end up with a system which is almost certainly vulnerable to attack, but in a more subtle way.

Using parameterized queries is the way to fix this. Any time you spend trying to take shortcuts to avoid fixing the problem properly is simply wasted time. Bite the bullet, and do it now. Maybe you need to "down tools" and do nothing else until this is fixed - or maybe you can pick off one query a day to fix, getting on with other features at the same time. Either way, I don't think you should spend any more time just ducking the issue.

Before you respond saying you can't go ahead and fix the issue properly, work out what's preventing this, and how you can remove those blockages. Is your management aware of the current risk (which goes well beyond just the server crashing, of course)? Is the problem that you feel you don't have the time to commit to fixing it, or some other obstacle? Is it political or technical? Again, I'd urge you to consider the long term benefit of the application. I've seen lots of situations where people have made a short term "hacky" fix and regretted it - but I can't remember ever seeing someone regretting doing the right thing for code which still has a significant period of life left, painful as it can be in the short term.

Answer 2

The regex [A-Za-z]+ matches one or more characters from A to Z (upper or lower case). You may reject a text that doesn't match this regex.

If you want to find all non-alphabet characters, use the regex [^A-Za-z] which will match any occurrence of a non-alphabetic character. Regex.Matches(inputString,"[^A-Za-z]") gives a MatchCollection, every Match in which will be for a non-alphabetic character.

[A-Za-z] is a character class allowing any letter and adding ^ immediately after the [ will match any character other than those specified in the character class.

Answer 3

using System.Text.RegularExpressions;

Regex pattern (only alphabetical): (?<=\[)[A-Za-z]+(?=\])

Use:

var matches = Regex.Matches(input, @"(?<=\[)[A-Za-z]+(?=\])");

EDIT: Yes, you should use parameterized queries, but in case you want to catch other characters 'real time' just prepare a TextChanged event:

private void textBox1_TextChanged(object sender, EventArgs e)
    {
        Match matches = Regex.Matches(input, @"(?<=\[)[A-Za-z]+(?=\])")
        if(!matches.Success)
        {
        MessageBox.Show("Invalid input.", Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
        textBox1.Text = "";
        }
    }

So in case one type a non-alphabetical character gets an error message immediately and the textbox is being cleared.

Answer 4

this will work i used in my applycations many times
it's c# WPF tested

   private void textBox1_TextChanged(object sender, TextChangedEventArgs e)
    {
        var tb = sender as TextBox;
        //allowed char's are 0-9, a-z, A-Z, germanChars, '-' and as extra the {0,40} part where you are able to define the Min and Max Char's
        var matches = Regex.Matches(tb.Text, @"^[0-9a-zA-ZäöüßÄÖÜß''-'\s]{0,40}$");
        if (!(matches.Count > 0))
        {
            MessageBox.Show("Invalid input.", "Text", MessageBoxButton.OK, MessageBoxImage.Error);
            textBox1.Text = "";
        }
    }

Answer 5

You can always remove unnecessary characters from the input, leaving only the ones you allow. I created a simple class with a couple of methods that I used in one of my apps.

public static string ToLettersAndDigits(this string text)
{
    StringBuilder sb = new StringBuilder();

    foreach (char c in text)
        if (char.IsLetterOrDigit(c) || char.IsWhiteSpace(c))
            sb.Append(c);

    return sb.ToString().Trim();
}

public static string ToLetters(this string text)
{
    StringBuilder sb = new StringBuilder();

    foreach (char c in text)
        if (char.IsLetter(c) || char.IsWhiteSpace(c))
            sb.Append(c);

    return sb.ToString().Trim();
}

public static string ToDigits(this string text)
{
    StringBuilder sb = new StringBuilder();

    foreach (char c in text)
        if (char.IsDigit(c))
            sb.Append(c);

    return sb.ToString().Trim();
}

It checks every char for a valid input, if it is invalid, it does not add it to the StringBuilder. It would probably be best on fields like "Name", where you do not have thouthands of charachters. Once you have this class, you can simply write resultString = yourString.ToLetters() and you will end up with only letters. Not the most elegant solution but it works!

EDIT: You can also add punctuation,

foreach (char c in text)
        if (char.IsLetterOrDigit(c) || char.IsWhiteSpace(c) || c == '.' || c == ',' || c == '!')
            sb.Append(c);