apostrophes in text being inserted into sql

C

cj2

I'm executing a web service call which returns a structure of stings. I
then use the below insert command to store the data in a sql table. My
problem is several pieces of information sometimes contain apostrophes
and though I haven't found them yet possibly parenthesis. An example
just occurred where the full_name and Last_name contained O'Conner.
This causes and exception to be thrown. How do I get around this problem?

Dim MyInsertCmd As New SqlCommand("insert into MyCo.dbo.MyTable " & _
"(account, record_length, number_of_replies, full_name, last_name, " & _
"first_name, address, city, state, zip) " & _
"Values ('" & results.account & "', " & _
"'" & results.record_length & "', " & _
"'" & results.number_of_replies & "', " & _
"'" & results.full_name & "', " & _
"'" & results.last_name & "', " & _
"'" & results.first_name & "', " & _
"'" & results.address & "', " & _
"'" & results.city & "', " & _
"'" & results.state & "', " & _
"'" & results.zip & "')", MySqlConnection)
MySqlConnection.Open()
MyInsertCmd.ExecuteNonQuery()
MySqlConnection.Close()
 
L

Lloyd Sheen

cj2 said:
I'm executing a web service call which returns a structure of stings. I
then use the below insert command to store the data in a sql table. My
problem is several pieces of information sometimes contain apostrophes and
though I haven't found them yet possibly parenthesis. An example just
occurred where the full_name and Last_name contained O'Conner. This causes
and exception to be thrown. How do I get around this problem?

Dim MyInsertCmd As New SqlCommand("insert into MyCo.dbo.MyTable " & _
"(account, record_length, number_of_replies, full_name, last_name, " & _
"first_name, address, city, state, zip) " & _
"Values ('" & results.account & "', " & _
"'" & results.record_length & "', " & _
"'" & results.number_of_replies & "', " & _
"'" & results.full_name & "', " & _
"'" & results.last_name & "', " & _
"'" & results.first_name & "', " & _
"'" & results.address & "', " & _
"'" & results.city & "', " & _
"'" & results.state & "', " & _
"'" & results.zip & "')", MySqlConnection)
MySqlConnection.Open()
MyInsertCmd.ExecuteNonQuery()
MySqlConnection.Close()
Click to expand...

Use SQLParameters. That will solve your problem and is a much better (safer
SQL Injection) way of sending info to SQL Server.

LS
 
S

Stewart Berman

Wrap the strings with a Replace(<string>,"'","''"). For example change:
"'" & results.full_name & "', " & _
to
"'" & Replace(results.full_name,"'","''") & "', " & _
The above replaces all single ' with two '.
 
S

sloan

//(<string>,"'","''"). //

Do NOT do this.
Please do NOT advise people to do this.


As already suggested:

//
Use SQLParameters. That will solve your problem and is a much better (safer
SQL Injection) way of sending info to SQL Server.//

............
 
C

Cor Ligthert[MVP]

Stewart,

I could not find a worse reply

Cor

Stewart Berman said:
Wrap the strings with a Replace(<string>,"'","''"). For example change:
"'" & results.full_name & "', " & _
to
"'" & Replace(results.full_name,"'","''") & "', " & _
The above replaces all single ' with two '.
Click to expand...
 
J

Jack Jackson

While I agree that using parameters is the way to handle this, what is
wrong with substituting two quotes for one?
 
S

Stewart Berman

I consider parameterizing the SQL but, after looking at sample code decided that a quick and dirty
approach that minimized changes in code and logic was appropriate and consistent with the coding
style.

The best method would be to have a parameterized stored procedure to provide the correct security
context and audit trail. Allowing dynamic SQL to change data in a database is a problem to begin
with and, unfortunately, .Net not only encourages it but requires it. Disconnected datasets send
changes back to the server as SQL statements meaning that dynamic SQL has to be permitted to change
data. Please note that I am not trying to start a flame war -- just stating my opinion.
 
C

Cor Ligthert[MVP]

Stewart,

parameters has nothing to do with Dynamic Sql and Stored procedures.
You simply can use parameters in dynamic Sql in the same way as with
Procedures.

However, at least are parameters more clean when it becomes to maintenance
and don't give this kind of problems like the OP has.

We see these problems as well forever when it is about a datetime when many
persons try to do it in all kind of difficult ways, while parameters are
just quiet simple to use.

Cor
 
C

Chris Diver

Jack said:
While I agree that using parameters is the way to handle this, what is
wrong with substituting two quotes for one?
Click to expand...

One example....

MySql backend. Single quotes can be escaped with a backslash. So in this
case I could inject \';DELETE FROM TABLE; The first single quote would
be escaped and the one kindly added would close the string and allow me
to inject SQL.

Chris.
 
L

Lloyd Sheen

Stewart Berman said:
You have lead a sheltered life.
Click to expand...

Person insults are never welcome. If you can't resolve that Cor and I are
trying to help then please google around and you will find more info about
this problem than you will have time to read.

In fact the addition of SQLParameter will make the code more readable,
easier to maintain and much more accurate since attempting to find out how
to format things for a dynamic SQL statement is much more difficult and
error prone
than the SQLParameter route.

Simply change the insert statement as follows:

Dim MyInsertCmd As New SqlCommand("insert into MyCo.dbo.MyTable " & _
"(account, record_length, number_of_replies, full_name, last_name, " & _
"first_name, address, city, state, zip) & _
values (@account, @record_length, @number_of_replies, @full_name,
@last_name, " & _
"@first_name, @address, @city, @state, @zip)

Then a set of:

MyInsertCmd.Parameters.Add(new SQLParameter((@account , results.account )

etc.

This method means that the parameters created do not have to be added in the
order of the SQL statement but will be matched to the parameter name. In
the long run this will work out best and be the easiest to maintain.

LS
 
H

Herfried K. Wagner [MVP]

cj2 said:
I'm executing a web service call which returns a structure of stings. I
then use the below insert command to store the data in a sql table. My
problem is several pieces of information sometimes contain apostrophes and
though I haven't found them yet possibly parenthesis. An example just
occurred where the full_name and Last_name contained O'Conner. This causes
and exception to be thrown. How do I get around this problem?

Dim MyInsertCmd As New SqlCommand("insert into MyCo.dbo.MyTable " & _
"(account, record_length, number_of_replies, full_name, last_name, " & _
"first_name, address, city, state, zip) " & _
"Values ('" & results.account & "', " & _
"'" & results.record_length & "', " & _
"'" & results.number_of_replies & "', " & _
Click to expand...

I strongly recommend to use a parameterized command object instead (VB
sample code included):

SqlCommand.Parameters Property (System.Data.SqlClient)
<URL:http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx>

Escaping will be done automatically and the solution will be safe regarding
SQL injection attacks.
 
S

Stewart Berman

Lloyd Sheen said:
Person insults are never welcome. If you can't resolve that Cor and I are
trying to help then please google around and you will find more info about
this problem than you will have time to read.
Click to expand...

Are you referring to the insult Cor made about my original suggestion?

As to my comment that he has led a sheltered life -- if my code is the worst possible reply he could
find then he has led a sheltered life. In the forty years I've being working with computers I have
come across much worse solutions to simple problems.
In fact the addition of SQLParameter will make the code more readable,
easier to maintain and much more accurate since attempting to find out how
to format things for a dynamic SQL statement is much more difficult and
error prone
than the SQLParameter route.

Simply change the insert statement as follows:

Dim MyInsertCmd As New SqlCommand("insert into MyCo.dbo.MyTable " & _
"(account, record_length, number_of_replies, full_name, last_name, " & _
"first_name, address, city, state, zip) & _
values (@account, @record_length, @number_of_replies, @full_name,
@last_name, " & _
"@first_name, @address, @city, @state, @zip)

Then a set of:

MyInsertCmd.Parameters.Add(new SQLParameter((@account , results.account )

etc.

This method means that the parameters created do not have to be added in the
order of the SQL statement but will be matched to the parameter name. In
the long run this will work out best and be the easiest to maintain.

LS
Click to expand...

Now that is helpful as you have explained how to parameterize the SQL statement instead of just
saying parasitize it.
 
S

Stewart Berman

Anytime you concatenate strings provided by the user you have a risk of an SQL injection attack. My
suggestion does not introduce the exposure. The original code has the same exposure.

By the way: http://msdn.microsoft.com/en-us/library/ms998271.aspx

Says at the bottom of the page:

Additional Considerations
Other things to consider when you develop countermeasures to prevent SQL injection include:

Use escape routines to handle special input characters.
Use a least-privileged database account.
Avoid disclosing error information.

Use Escape Routines to Handle Special Input Characters
In situations where parameterized SQL cannot be used and you are forced to use dynamic SQL instead,
you need to safeguard against input characters that have special meaning to SQL Server (such as the
single quote character). If not handled, special characters such as the single quote character in
the input can be utilized to cause SQL injection.

Note Special input characters pose a threat only with dynamic SQL and not when using parameterized
SQL.
Escape routines add an escape character to characters that have special meaning to SQL Server,
thereby making them harmless. This is illustrated in the following code fragment:

private string SafeSqlLiteral(string inputSQL)
{
return inputSQL.Replace("'", "''");}
}


Perhaps you can suggest that Microsoft change their recommendation.
 
C

cj2

I agree, this is the most helpful post. I might have a question on it
tomorrow though.

P.S. I asked around here and was told to use Stewart's approach. :)
Still if for no other reason than learning something new I will
probably try to do the parameterized statement first. I did some code 3
years ago that looks similar to Lloyd's example but I don't understand
it now--hence I might have questions tomorrow.
 
L

Lloyd Sheen

cj2 said:
I agree, this is the most helpful post. I might have a question on it
tomorrow though.

P.S. I asked around here and was told to use Stewart's approach. :) Still
if for no other reason than learning something new I will probably try to
do the parameterized statement first. I did some code 3 years ago that
looks similar to Lloyd's example but I don't understand it now--hence I
might have questions tomorrow.
Click to expand...


Take a look at the following link. It has a full example of using an INSERT
with parameters. The example is in VB.NET.

http://vbnetsample.blogspot.com/2007/10/using-sqlparameter-class.html

Hope this helps
LS
 
S

Stewart Berman

Perhaps I was not clear. The first statement about considering parameterizing the SQL is not
dependent on the second one about the best approach.

By definition, dynamic SQL consists of SQL statements sent in source form and compiled (or prepared)
and executed on the server. Dynamic simply means that they are constructed outside the server -- by
whatever means -- and sent to the server in source form. They may be parameterized or they may not
be.

They represent a potential security risk because, by definition, the server does not fully control
what they can do and does not maintain an audit trail of their effects. Whether they are used from
an ASP.NET web page or a locked up compiled program they still introduce another point of security
failures and maintenance problems since they are separated from the controls over the database and
the objects therein.

Please don't get me wrong -- I understand the need for it. Usually it is because the front end
developers are closer to the end user and have to respond faster than the database developers.
Given that, I agree that parameterized dynamic SQL is better than concatenating strings but using a
stored procedure is better still.

On a side note the problem of embedded single quotes goes back well before the web existed. One
classic method was to translate all single quotes to double quotes on the way in and double quotes
to single quotes on the way out. O'Conner would be stored as O"Conner.
 
J

J.B. Moreno

Stewart Berman said:
They represent a potential security risk because, by definition, the
server does not fully control what they can do and does not maintain
an audit trail of their effects. Whether they are used from an
ASP.NET web page or a locked up compiled program they still introduce
another point of security failures and maintenance problems since
they are separated from the controls over the database and the
objects therein.
Click to expand...

Server control and audit trails are ultimately dependent upon how the
server is setup, not whether or not the SQL is dynamic or not.


-snip-
I agree that parameterized dynamic SQL is better than concatenating
strings but using a stored procedure is better still.
Click to expand...

If you concatenate the string that represents your stored procedure,
it's no better than any other piece of dynamic SQL.
 
C

Cor Ligthert[MVP]

Steward,

What is better or worse is mostly very subjective.

However, in SQL server a stored procedure will be cached. Therefore as you
have transact code which is very intensively used, then the sproc can be a
better choose but that does not imply that it is forever a better choose, as
it is because of maintenance and deployment, then dynamic SQL what in a way
is Linq as well, can be a better choice.

Cor
 
Top