I get a SQL error when using single quotes in ASP.Net

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!

• Status: Open
• Question Views: 2613
• Answer Count: 9
• Vote Up 23 Vote Down

• Answer Accepted: Yes
• Question Category: .NET Programming

Best Answer by Stella

Go To Solution

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.

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: .NET Programming

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 ?

About Brigetta

Questions
1

Answers
4

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: .NET Programming

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 ?

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: .NET Programming

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()

About Brigetta

Questions
1

Answers
4

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: .NET Programming

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.

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: .NET Programming

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… 🙁

About Brigetta

Questions
1

Answers
4

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: .NET Programming

Best Answer

Best Answer

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.

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: .NET Programming

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!

About Brigetta

Questions
1

Answers
4

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: .NET Programming

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!

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: .NET Programming