I am developing a CMS application in .Net. I have this problem that whenever a single quote comes in the data, I get an SQL error in the application! Please help!
What you are experiencing is termed as "SQL Injection". It is a method where SQL code is directly injected into the business logic from the user interface.
This happens when you are using SQL statements that are created by string concatenation. The single quote character is used to encapsulate a string data when used in the WHERE clause of the statement. Therefore it is possible to send a string with a single quote to terminate the WHERE clause and continue the WHERE clause subsequently.
You mean someone could play around with my database just by typing SQL commands from my text area control on the front end?
This is very alarming! How can they do it ?
In order to show you an example, could you let me know if you are using C# or VB in your ASP.Net project ?
I am using VB.Net as the programming language.
The following is the SQL statement that is causing this problem:
Dim strSQL as String
strSQL = "select * from cms_content where title='" & TextBox1.Text & "''"
cmd.CommadText = strSQL
cmd.Open()
Thank you for the information, Brigetta.
Now, if you look at the SQL statement closely, I could type the following text in the Text1 textbox:
' OR 1=1 OR ''='
Notice the starting single quote and the trailing single quote. This results in the following SQL statement:
strSQL = "select * from cms_content where title='' OR 1=1 OR ''='"
I can replace the 1=1 with any SQL statement I wish for. I can even do the following in the text box:
'; DROP database master; SELECT '
This will give me the following statement:
strSQL = "select * from cms_content where title=''; DROP database master; SELECT ' "
I think you will know the result of this :). This is what is called SQL injection. Single quote is the culprit.
GOOD HEAVENLY LORD! That is disastrous!
Please tell me how I can prevent this from happening! I can see my job is hanging from the clothesline… 🙁
You can use parameters instead of string concatenation
strSQL = "select * from cms_content where title=@Title"
cmd.CommanText = strSQL
cmd.Parameters.AddWithValue (@Title, Text1.Text)
cmd.Open
Using the above code, the single quote will go as data instead of an SQL command.
THANK YOU!!! IT WORKS!
No more errors! You are wonderful!
Glad to be of help! Have a nice day!
