Stop the user entering ' char

Question

I have a search page where I would like to stop the user entering a ' into textboxes, or replace it with a suitable character. Can anyone help me achieve this in asp.net vb ?

For example if a user searches for O'Reilly the search crashes with error:

Line 1: Incorrect syntax near 'Reilly'. Unclosed quotation mark before the character string ' '.

Thanks!

Answer 1

Use parameterized statements properly, and this will be handled for you.

Answer 2

Uh-oh. Use parameterized queries.

Answer 3

You can escape ' character with two of them '', e.g.

sql += "Surname LIKE '%" & name.Replace("'", "''") & "%' AND "

and SQL will accept it then.

However, I would suggest using parameters.

Answer 4

Use javascript onKeyDown event for the textbox - if the typed char is an apostrophe, you can simply drop it, so that it is not entered.
On the server side, you should simply replace "'" with "", just to make sure.
Be aware, that this is a very unsecure and unstable solution.

Answer 5

To actually answer the question, you can put an OnKeyDown javascript event on your textbox, detect the key that was pressed, and potentially cancel the input:

<input class="mainSearchBox" type="text" id="searchTerm" onkeydown="DetectIllegalKeys();">

<script>
function DetectIllegalKeys() {
    if (event.keyCode == 222) {
        event.returnValue = false;
    }
}
</script>

to instead change apostrophes to an alternate character:

<input class="mainSearchBox" type="text" id="searchTerm" onkeyup="ChangeSingleQuote();">

<script>
function ChangeSingleQuote() {
    var searchTerm = document.getElementById('searchTerm');
    searchTerm.value = searchTerm.value.replace(/'/g, "e"); 

}

I highly recommend that you not use this approach for this problem! Far better to fix the application to allow searches for titles of any character string.

Answer 6

You need to understand why the error occurs and not just solve it symptomatically. Read through Microsoft's own document about SQL Injection to find out how to protect yourself from this class of security flaws.

http://msdn.microsoft.com/en-us/library/ms998271.aspx

(As several others pointed out, parameterized statements is the solution.)

Answer 7

it needs revision