database error within a search

Question

Hi,

I am doing a site in asp.

when i start a search with

a name like this o'neil

i'm getting a database error.

i'm getting this error because of that ' in that name.

how can i remove this error in asp.

if its in asp we can use addslashes and stripslashes but how can i do this in asp???

Please help

Answer 1

Whether it is for SELECTing a particular string value or for INSERTing it into the database, the string must be valid for SQL, specifically:

• The start of the string is marked by a single quote character
• Any single quote that is part of the string must be doubled (lest SQL understands it as the end of the string)
• The end of the string is marked by a single quote.

Therefore

  'O''Neil'

is the proper way to "code" the name O'Neil, because if we used

  'O'Neil'

instead, SQL would see this as a string, starting with the letter O, but ending there after. This single letter string is then followed by the expression Neil' which SQL cannot understand.

---

The above information will allow you to store the names and to query for them in a way which preserves the single quotes embedded in names as with the Irish O'xxxx.
Beyond helping you fix this situation, I'd be remiss if I forgot to mention the risks of SQL injection.

Now that you understand that a non-doubled single quote will terminate the string, you can see how malicious users may use this to "trick" the application.
For example let's say the user somehow guessed that the table in use is search, he/she may go ahead and fill in the following string in the edit box of the application

  O'; DELETE FROM search where 'a%'='a

Your logic will use this string as follow

Lstart="O'; DELETE FROM search where 'a%'='a"; select * from search where lname like '%Lstart%'
-- which effectively will be substituted as
select * from search where lname like '%O';  DELETE FROM search where 'a%'='a%'

Which, if the account associated with the underlying SQL connection is so authorized , will result in deleting all rows from the table, and leaving your application broken !

By systematically doubling the single quotes, and with a few other precautions, such as the use of parametrized queries, it is possible to protect the application (and its database) from this type of attacks.