ASP SQL Single Quote

[T111]

It took me ages to figure this one so I said i would share. Just a note its much better to avoid using sql directly from asp e.g. “select * from table 1 where name =’” & name & ‘” or “insert into table1 (name) values (‘” & name & “’). User Stored procedures and parameters

Problem
When passing a single quote to select statement e.g. username = O’Neill

Solution
1. Create a stored procedure in SQL

CREATE PROCEDURE GetUserDetails(@value nvarchar(100))
as
SELECT *
FROM dbo.users
where username like '%' + @value + '%'

2. Setup 2 Global functions (can use replace but I like to know exactly whats going on
(a) works when performing insert
Public Function RemoveQuote(ByVal TheString As String)
RemoveQuote = ""
Dim z
z = Split(TheString, "'")
If UBound(z) > 0 Then
Dim i As Integer
Do While i <= UBound(z)
RemoveQuote = RemoveQuote & "'" & z(i) & "'"
i = i + 1
Loop

Else
RemoveQuote = "'" & TheString & "'"
End If

End Function


(b) works when performing select

Public Function RemoveQuoteForSelect(ByVal TheString As String)
RemoveQuoteForSelect = ""
Dim z
z = Split(TheString, "'")
If UBound(z) > 0 Then
Dim i As Integer
Do While i <= UBound(z)
RemoveQuoteForSelect = RemoveQuoteForSelect & "'" & z(i) & "'''"
i = i + 1
Loop

Else
RemoveQuoteForSelect = "'" & TheString & "'"
End If

End Function

Step 3
From asp.net here is the select
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
Dim cnn As Data.SqlClient.SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("myConnString").ConnectionString)
cnn.Open()

Dim reader As Data.SqlClient.SqlDataReader
Dim SQL As String
Dim x As New Globals
Dim un = x.RemoveQuoteForSelect(Me.TextBox1.Text)
SQL = "GetUserDetails " & un
Dim cmd As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand(SQL, cnn)
reader = cmd.ExecuteReader()

If reader.HasRows() Then
reader.Read()
Me.TextBox1.Text = reader(2)
End If

reader.Close()
cnn.Close()
End Sub

Step 4
From asp.net here is the insert (setup a parameter insert procedure) just reference the global variable like this
Dim x as new Globals
cmd.Parameters.Add("@Username", SqlDbType.VarChar, 255).Value = x.RemoveQuote(me.txtUsername.text)


Of Course you will need to perform checks etc this is just the main steps
Happy Coding

 

[T111]

Problem above cant use like
but SQL has built in functions



String of ? 128 characters


QUOTENAME(@variable, '''')




String of > 128 characters


REPLACE(@variable,'''', '''''')

 

[gbaughma]

Problem
When passing a single quote to select statement e.g. username = O'Neill

Solution
Sterilize the input before it ever gets to SQL - also prevents injection.

sTmp = "O'Neill"
sTmp = Replace(sTmp,"'","''")
sTmp = Replace(sTmp,Chr(34),"''")



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[jmeckley]

stored procs don't solve the problem of injected sql. a parameterized query will. whether the command text is dynamic sql or a stored proc, it doesn't matter the inputs are parameters, not injected strings.

using(var command = connection.CreateCommand())
{
   command.CommandText = "select * from table where column like @value";
   var parameter = command.CreateParameter();
   parameter.DbDataType = DbDataType.String;
   parameter.Value = string.Format("%{0}%", the value);
   command.Parameters.Add(parameter);

   var results = new DataTable();
   using(var reader = command.ExecuteReader())
   {
      results.Load(reader);
      return results;
   }
}

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[T111]

Yes I agree, got confused while trying to figure this out. When I used parameters it works fine. I created a global function to fix any field that has a quote, then pass that “Fixed” value to the stored procedure as a parameter.

Here is the working example I used to ensure the name is not already in the system (This name could be o'neill for example). "GetDetailsByName" is a stored Procedure

Dim cnn As Data.SqlClient.SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("MyConn").ConnectionString)
cnn.Open()
Dim reader As Data.SqlClient.SqlDataReader
Dim SQL As String
Dim x As New Globals
Dim FixedName as string = x.RemoveQuote(me.txtName.text)
SQL = "GetDetailsByName " & FixedName
Dim cmd As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand(SQL, cnn)
reader = cmd.ExecuteReader()
If reader.HasRows()
Duplicate Record

End if

reader.Close()
cnn.Close()

The problem I was having is that I always used SQL statements to run everything and never used parameters. Now I realise using parameter values is much easier to maintain and execute. So if it’s a stored procedure or a SQL command executed directly from code, I use parameters when string/date values are passed.

 

[jmeckley]

no, your still missing the point. you always use a parameterized query. your code above still suffers from a sql injection attack.

command.CommandType = CommandType.StoredProcedure;
command.CommandText = "GetDetailsByName @input";
var p = command.CreateParameter();
p.value = txtName.text;
command.Parameters.Add(p);

command.ExecuteReader();

it's doesn't matter what type the value has, string, int, date, etc.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[T111]

Thanks for your feedback

2 Questions

1. The code I have works even if a quote exists, I've tested it and it correctly identifies that the name exists

2. Is it not better to use direct sql statements from code (if you can) to ensure the program runs faster
e.g.
"Delete * from tblMain where ID = " & myID

or do you think its better (an no slower performance issues) to parameterize everything?


Thanks

 

[jmeckley]

1. yes it does, but you are open to sql injection attacks.
2. it's not about preformance, it's about preventing sql injection attacks.

there are ways to optimize data access. simply moving sql statements from code to a stored procedure isn't it.
to improve data access
1. prepare ado.net commands before execution
2. batch reads and writes when possible
3. ensure the database is properly indexed
4. avoid select n+1 scenarios
5. limit the amount of data returned from a query
6. scope the connection and/or transaction to the business context (not to small and not to large)

Jason Meckley
Programmer

faq855-7190
faq732-7259