Solved

Single Quote Breaks SQL EXEC Parameter

Posted on 2011-02-17
7
1,455 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

726 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