Solved

Single Quote Breaks SQL EXEC Parameter

Posted on 2011-02-17
7
1,423 Views
Last Modified: 2012-05-11
Quick background:
I created a Database Class which is supposed to handle all db connections.
I pass this class Queries and Execute Commands.
I have one function in the Database Class that returns a dataset based on the query or exec command i send it.
I have been making an attempt to integrating some SQL Injection Prevention Methods.

One area where I am confused is how SQL Injection prevention deals with Single Quotes and SQL Parameters. Which is what this question is about.

So, in my web form let's say i grab a value from a text box, make sure it does not contain any "dangerous" characters or phrases, then pass that value on through to the Database Class one of two ways, depending on the complexity of the query needed.

The first way is as follows, using Parameters defined in the code behind:

        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Dim sqlConnectionString As String = objDatabase.sqlConnect(strsqlConnectionString)

        con.ConnectionString = sqlConnectionString
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "SELECT EmailAddress FROM [User] WHERE EmailAddress = @EmailAddress"
        Dim parEmailAddress = New SqlParameter("@EmailAddress", SqlDbType.VarChar)
        parEmailAddress.Value = strEmailAddress
        cmd.Parameters.Add(parEmailAddress)
        cmd.ExecuteNonQuery()
        Dim sqlreader As SqlDataReader = cmd.ExecuteReader()

Open in new window


The other way I want to do it is to call a stored procedure like this:

        Dim sqlSelect As String = "EXEC spCheckForEmail '" & strEmailAddress & "'"
        Dim ds As New DataTable

        ds = objDatabase.getSQL(sqlSelect, strsqlConnectionString)

Open in new window


Now, the trouble is this:
If I send a textbox value that contains a single quote to the stored procedure I get the following error:

Incorrect syntax near '7'.
Unclosed quotation mark after the character string ''.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '7'.
Unclosed quotation mark after the character string ''.

Open in new window


Now, the first way using code behind parameters does not error out and I get the information i need out of the database. The second way throws this error.

Before anyone tells me to escape the single quotes -- i know i could do that -- but i was thinking that if i am sending the variable as a parameter to a stored procedure -- shouldn't SQL be able to handle a single quote?

Here is the Function that returns the dataset:
The error bubbles up in the adapter.Fill line.
    Public Function getSQL(ByVal strSQLSelect As String, ByVal strConnectionStringName As String) As DataTable

        sqlConnectionString = sqlConnect(strConnectionStringName)
        Dim adapter As New SqlDataAdapter(strSQLSelect, sqlConnectionString)
        Dim ds As DataSet = New DataSet()

        adapter.Fill(ds, "TempTable")
        Return ds.Tables("TempTable")

    End Function

Open in new window


I think my problem actually occurs here:
Dim sqlSelect As String = "EXEC spCheckForEmail '" & strEmailAddress & "'"

Open in new window


... because when this is sent to the getSQL Function in the Database class it is actually being rendered like this:

Dim sqlSelect As String = "EXEC spCheckForEmail 'someemail'7@domain.com'

Open in new window


So, the rendered EXEC above actually has a malformed command almost containing two parameters without a comma separating the two.

Short of sending every string through a .Replace Single Quote Function, is there anyway I can prep the EXEC Command Parameters before it is sent to the Database Class and the stored procedure is actually called at the database?

Can I still use the getSQL Function to return a dataset safely?

Sorry for the long post -- but I wanted to be as clear as possible and provide as much information as possible up front.

Thanks in advance.
Jason

0
Comment
Question by:jsvb1977
7 Comments
 
LVL 3

Expert Comment

by:sunezapa
ID: 34922712
I did not get it all ;-)  but had an eye on the "If I send a textbox value that contains a single quote..."
I would never send single quotes to SQL, but replace all single quotes with two single quotes: replace(txt,"'","''")
Is this not a part of your "make sure it does not contain any "dangerous" characters" ?
0
 

Author Comment

by:jsvb1977
ID: 34922924
Yes, I can use this function:

    Public Function fncReplaceQuote(ByVal strToReplace As String) As String
        strToReplace = strToReplace.Replace("'", "''")
        Return strToReplace
    End Function

Open in new window


But I was thinking that it would be possible to mimic the safety of sql parameters / stored procedures as i have done here:

       Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Dim sqlConnectionString As String = objDatabase.sqlConnect(strsqlConnectionString)

        con.ConnectionString = sqlConnectionString
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "SELECT EmailAddress FROM [User] WHERE EmailAddress = @EmailAddress"
        Dim parEmailAddress = New SqlParameter("@EmailAddress", SqlDbType.VarChar)
        parEmailAddress.Value = strEmailAddress
        cmd.Parameters.Add(parEmailAddress)
        cmd.ExecuteNonQuery()
        Dim sqlreader As SqlDataReader = cmd.ExecuteReader()

Open in new window


... and ultimately circumnavigate the need to use the REPLACE function.

Is that a bad idea?
Should I always run all user input through the REPLACE function before sending it to SQL?

And if so, what happens to the single quote which is now a double quote? Will this not potentially cause a problem?

For example:

If I have a field in sql that contains a single quote like this:  O'Neil
And the user enters a value into the text box of:  O'Neil
And then I run that value through the replace function, so the value becomes: O''Neil
Then my query becomes, SELECT * FROM [Table] WHERE [Name] = 'O''Neil'
O'NEIL does not equal O''Neil -- so nothing will be returned.

If I am able to circumnavigate the REPLACE function and simply pass along the value SAFELY to SQL with only a single quote -- then that condition will be met.

Now, in the code block above, I am able to do this using the SQL Parameter in code behind.
But this does not work when passing that variable in an EXEC stored procedure.

One last thing -- If I EXECUTE the stored procedure from SQL Management Studio directly and enter O'NEIL into the parameter prompt, the stored procedure will EXECUTE without error.

What if I was able to do something like:
EXEC storedprocedure @Parameter

Open in new window

I tried this without success.

Maybe it is not possible?
Maybe I really do need to clean all strings before they are sent to SQL?

Thanks for the prompt reply above.

0
 
LVL 3

Assisted Solution

by:sunezapa
sunezapa earned 333 total points
ID: 34923158
for the parameters you need to use '' for a '  - just my experience, and I did not test so deep, as it still works ;-)
sometimes... I repalce the ' with `or ` at user input, just to avoid any   problems, but this is plain lazyness, and only possible when suer's don't care ;-)

if not using Stored Procedures you may open a recordset for add/edit and enter the values in the recordset and do and update.
This will not involve SQL and you can enter "any value" without handling special characters.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Expert Comment

by:pavan_kumar_kokkiragedda
ID: 34923753
try  replacing single quote( ' ) with char(39)

hope this helps
0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 167 total points
ID: 34923998
You are right about parameters being the best way from clients sending queries to sql server.
You are wrong in your assumption that SELECT * FROM [Table] WHERE [Name] = 'O''Neil' would not find O'Neil, as in fact the escaping of single quotes as double qoutes is done so that SQL Server in fact searches for O'Neil and not O''Neil.

To profit from the safety of parameters, take a look here:
http://www.c-sharpcorner.com/UploadFile/dclark/UseSPwithDP11282005035417AM/UseSPwithDP.aspx

You create a SqlCommand, set the SqlCommand..CommandType to CommandType.StoredProcedure and use the SqlCommand as the SqlDataAdapter.SelectCommand

More details on the lined article.

Bye, Olaf.

0
 

Author Comment

by:jsvb1977
ID: 34945583
sunezapa:

if not using Stored Procedures you may open a recordset for add/edit and enter the values in the recordset and do and update.
This will not involve SQL and you can enter "any value" without handling special characters.

Would you mind elaborating on this just a little bit more for me?

Jason
0
 
LVL 3

Assisted Solution

by:sunezapa
sunezapa earned 333 total points
ID: 34957758
sorry, I was travelling...

have a look on http://www.classanytime.com/mis333k/sjdaoadorecordsets.html
go for the ADO, I would, in this case...

the essence:
open the recordset (ADO is automatical open for editing, for adding use rs.addNew first)
Dim rs As New ADODB.Recordset
rs.Open "SELECT pkPeopleID, LastName FROM tblPeople", CurrentProject.Connection, adOpenStatic

Open in new window

Editing:
 
rs("LastName") = "Smith-Jones's special character"
rs.Update

Open in new window


0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now