Asked By
Brigetta
230 points
N/A
Posted on - 05/16/2011
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!
Answered By
Stella
0 points
N/A
#98122
I get a SQL error when using single quotes in ASP.Net
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.
Answered By
Brigetta
230 points
N/A
#98123
I get a SQL error when using single quotes in ASP.Net
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 ?
Answered By
Stella
0 points
N/A
#98125
I get a SQL error when using single quotes in ASP.Net
In order to show you an example, could you let me know if you are using C# or VB in your ASP.Net project ?
Answered By
Brigetta
230 points
N/A
#98126
I get a SQL error when using single quotes in ASP.Net
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()
Answered By
Stella
0 points
N/A
#98127
I get a SQL error when using single quotes in ASP.Net
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.
Answered By
Brigetta
230 points
N/A
#98128
I get a SQL error when using single quotes in ASP.Net
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… 🙁
Answered By
Stella
0 points
N/A
#98129
I get a SQL error when using single quotes in ASP.Net
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.
Answered By
Brigetta
230 points
N/A
#98130
I get a SQL error when using single quotes in ASP.Net
THANK YOU!!! IT WORKS!
No more errors! You are wonderful!
Answered By
Stella
0 points
N/A
#98132
I get a SQL error when using single quotes in ASP.Net
Glad to be of help! Have a nice day!
