Apostrophe in SQL string

[Guest]

Hi,

in my application I add personal information about customers to a database
by sending a SQL string. Now, sometimes there are customers with names that
contain an apostrophe (like D'Artienne, etc.). Now, when I create a SQL
string with such an apostrophe it gets malformatted (like: "insert into
table1 (Firstname) values ('D'Artienne')" ). So, is there any way to insert a
name that contains an apostrophe into a table by SQL?


Thanks a lot
peter

 

[Cor Ligthert]

Peter

I assume you write it really as you showed and does not use the
SQLparameters?

http://msdn.microsoft.com/library/d...systemdatasqlclientsqlparameterclasstopic.asp

I would try those,

I hope this helps?

Cor

 

[Guest]

string sql = @"INSERT INTO table (name) VALUES('D'Andrea')";

The @ indicates a verbatim string literal so you don't have to escape
certain charaction - like backslashes and apostrophes.

See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnguinet/html/drguinet5_update.asp

Hope this help!

 

[Miha Markic [MVP C#]]

Hi,

No, this won't work at it will cause SQL error.
Peter should really use parametrised queries instead.

 

[Jos]

Hi,

in my application I add personal information about customers to a
database by sending a SQL string. Now, sometimes there are customers
with names that contain an apostrophe (like D'Artienne, etc.). Now,
when I create a SQL string with such an apostrophe it gets
malformatted (like: "insert into table1 (Firstname) values
('D'Artienne')" ). So, is there any way to insert a name that
contains an apostrophe into a table by SQL?


Thanks a lot
peter

If you really want to avoid parameterized queries
(see other replies), you can replace every apostrophy
with two apostrophes:

"... values ('" & Name.Replace("'","''") & "')"

 

[Pablo Castro [MS]]

In general, I would recommend against any technique to try to properly
escape values to be concatenated into SQL statements, it's just too hard to
get it right. It's far better to use parametrized queries. It's not just a
matter of potentially getting syntax errors, but more of a security issue,
as you open a door for SQL injection attacks by concatenating values
straight into the SQL statement.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Angel Saenz-Badillos[MS]]

To me saying "If you really want to avoid parameterized queries" sounds a
lot like "if you do not mind giving hackers full control of your
database/network". Never ever pass user data directly to the database,
always use parameters for any data passed from customers to avoid Sql
Injection attacks. As a nice side benefit we do the work of handling
D'Artienne and a lot of other names you have not yet considered.

Friends don't let friends get hacked with Sql Injection attacks.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/

 

[Guest]

Hi,

in my application I add personal information about customers to a database
by sending a SQL string. Now, sometimes there are customers with names that
contain an apostrophe (like D'Artienne, etc.). Now, when I create a SQL
string with such an apostrophe it gets malformatted (like: "insert into
table1 (Firstname) values ('D'Artienne')" ). So, is there any way to insert a
name that contains an apostrophe into a table by SQL?


Thanks a lot
peter

User submitted from AEWNET (http://www.aewnet.com/)

 

[Guest]

You've probably solved this by now. All you have to do is add another apostrophe beside the one you'd like to store e.g. insert into table XYZ (Firstname) values ('D''Artienne').

So in the table the column Firstname stores the value D'Artienne.

Cheers
T

Hi,

in my application I add personal information about customers to a database
by sending a SQL string. Now, sometimes there are customers with names that
contain an apostrophe (like D'Artienne, etc.). Now, when I create a SQL
string with such an apostrophe it gets malformatted (like: "insert into
table1 (Firstname) values ('D'Artienne')" ). So, is there any way to insert a
name that contains an apostrophe into a table by SQL?


Thanks a lot
peter

User submitted from AEWNET (http://www.aewnet.com/)

 

[Ranjan]

On Thu, 27 Jan 2005 01:55:05 -0800, Guest wrote:
use escape sequence for apostrophe (' x 2)

insert into
table1 (Firstname) values ('D''Artienne')

http://dotnetjunkies.com/weblog/dotnut

 

[W.G. Ryan eMVP]

USE PARAMATERIZED QUERIES!!!!!

You can use all sorts of work arounds by you aren't fixing the core problem.
And in all likelihood you or some other developer will forget to escape the
SQL String and it will break again - and in all likelihood it will be a user
in a production envirnoment that finds it.

Honestly - there's absolutely NO upside to concatenating SQL Strings like
this and there's tons of downside - security (Sql Injection Attacks),
performance (can't cache the execution plan the same way) , maintainability
(You will have to write extra code and always remember to safeguard against
it) - as opposed to using Paramaterized queries and being done with it.

 

[ranjan.listserv]

USE PARAMATERIZED QUERIES!!!!!

You can use all sorts of work arounds by you aren't fixing the core problem.
And in all likelihood you or some other developer will forget to escape the
SQL String and it will break again - and in all likelihood it will be a user
in a production envirnoment that finds it.

Honestly - there's absolutely NO upside to concatenating SQL Strings like
this and there's tons of downside - security (Sql Injection Attacks),
performance (can't cache the execution plan the same way) , maintainability
(You will have to write extra code and always remember to safeguard against
it) - as opposed to using Paramaterized queries and being done with it.

I agree, this also helps remove harmful sql query injections from hostile
clients, for asp.net application.

Ranjan

 

[Angel Saenz-Badillos[MS]]

This just cannot be repeated enough times.
DO NOT pass in customer data directly into a Sql Query. ALWAYS use
parameters.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/

You've probably solved this by now. All you have to do is add another

apostrophe beside the one you'd like to store e.g. insert into table XYZ
(Firstname) values ('D''Artienne').

 

[Miha Markic [MVP C#]]

Hi Angel,

I hope you have this answer in "favorites answers" as it will have to be
repeated many times
OTOH, I can imagine a scenario where you might have to pass in a dynamically
built WHERE clause instead of parameters:
IN clause is a very tempting candidate - when there is too many parameters.
There are workarounds, but they are all "not that easy" to employ.

As this is very annoying (dealing with parameter lists that is) I can only
wonder why there is (still) no support for parameter lists even in Yukon.
Can you shed some light on this?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

This just cannot be repeated enough times.
DO NOT pass in customer data directly into a Sql Query. ALWAYS use
parameters.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/

You've probably solved this by now. All you have to do is add another

apostrophe beside the one you'd like to store e.g. insert into table XYZ
(Firstname) values ('D''Artienne').

So in the table the column Firstname stores the value D'Artienne.

Cheers
T



User submitted from AEWNET (http://www.aewnet.com/)

 

[Angel Saenz-Badillos[MS]]

Miha,
I wonder the same thing, I guess the answer is that we have not been able to
come up with a good solution in this space. Do you have any suggestions? I
would be happy to pass them on.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/

Hi Angel,

I hope you have this answer in "favorites answers" as it will have to be
repeated many times
OTOH, I can imagine a scenario where you might have to pass in a dynamically
built WHERE clause instead of parameters:
IN clause is a very tempting candidate - when there is too many parameters.
There are workarounds, but they are all "not that easy" to employ.

As this is very annoying (dealing with parameter lists that is) I can only
wonder why there is (still) no support for parameter lists even in Yukon.
Can you shed some light on this?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

This just cannot be repeated enough times.
DO NOT pass in customer data directly into a Sql Query. ALWAYS use
parameters.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/

You've probably solved this by now. All you have to do is add another

apostrophe beside the one you'd like to store e.g. insert into table XYZ
(Firstname) values ('D''Artienne').

So in the table the column Firstname stores the value D'Artienne.

Cheers
T


Hi,

in my application I add personal information about customers to a database
by sending a SQL string. Now, sometimes there are customers with

names
that

contain an apostrophe (like D'Artienne, etc.). Now, when I create a SQL
string with such an apostrophe it gets malformatted (like: "insert into
table1 (Firstname) values ('D'Artienne')" ). So, is there any way to insert a
name that contains an apostrophe into a table by SQL?


Thanks a lot
peter

User submitted from AEWNET (http://www.aewnet.com/)

 

[Miha Markic [MVP C#]]

Hi Angel,

Miha,
I wonder the same thing,

This is so weird. I really can't figure why there is no solution to such an
annoying problem

I guess the answer is that we have not been able to

come up with a good solution in this space. Do you have any suggestions? I
would be happy to pass them on.

On the .net side you could add a flag to SqlParameter (or perhaps make it
generic to all parameter types)
when true one might pass an array as a SqlParameter.Value property.
Also some SqlParameter constructor overloads might come handy.

On the sql server side you might introduce an array parameter, like, for
example:
int[], varchar(50)[] which would be usable in stored procedures, for
example.

Note, the above solution is a product of brainstorming right before I go to
sleep

What do you think?
However, the question still remains on why this is such a big problem for
Sql Server.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/

Hi Angel,

I hope you have this answer in "favorites answers" as it will have to be
repeated many times
OTOH, I can imagine a scenario where you might have to pass in a dynamically
built WHERE clause instead of parameters:
IN clause is a very tempting candidate - when there is too many parameters.
There are workarounds, but they are all "not that easy" to employ.

As this is very annoying (dealing with parameter lists that is) I can
only
wonder why there is (still) no support for parameter lists even in Yukon.
Can you shed some light on this?

 

[Robbe Morris]

I agree with the poster below about "never" doing this.

For a variety of reasons, you have just opened up a major
security hole in your application even if you successfully
convert the apostrophes. You never know which "smart guy"
will start playing with your application just to prove how smart
they are and how dumb you are. You'd be surprised at the
number of "second cousins" that employees let play with
their work software/web sites.

The security hole you open up with dynamic sql strings would
be a cake-walk for any 12 year old who has an ounce of sense
about programming.

Is this the person you want to introduce to your boss as the
one who crashed your app or more likely deleted your data?

My intent is not to hurt your feelings, abuse you, or talk down
to you. Just to give a wake up call to someone I'm sure works
their tail off to build great software. I'd hate to see you get
embarrased.

I understand some applications may need to have different
update or insert statements for different situations. In these
cases, you could include an extra parameter in your
stored procedure that dictates which statements to process.
Include all possible input parameters and ensure that those
that are optional can be set as null or default values if they
aren't passed in.

--
2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
http://www.mastervb.net

Miha,
I wonder the same thing, I guess the answer is that we have not been able
to
come up with a good solution in this space. Do you have any suggestions? I
would be happy to pass them on.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/

Hi Angel,

I hope you have this answer in "favorites answers" as it will have to be
repeated many times
OTOH, I can imagine a scenario where you might have to pass in a dynamically
built WHERE clause instead of parameters:
IN clause is a very tempting candidate - when there is too many parameters.
There are workarounds, but they are all "not that easy" to employ.

As this is very annoying (dealing with parameter lists that is) I can
only
wonder why there is (still) no support for parameter lists even in Yukon.
Can you shed some light on this?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

message

This just cannot be repeated enough times.
DO NOT pass in customer data directly into a Sql Query. ALWAYS use
parameters.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/




You've probably solved this by now. All you have to do is add another
apostrophe beside the one you'd like to store e.g. insert into table
XYZ
(Firstname) values ('D''Artienne').

So in the table the column Firstname stores the value D'Artienne.

Cheers
T


Hi,

in my application I add personal information about customers to a
database
by sending a SQL string. Now, sometimes there are customers with names
that
contain an apostrophe (like D'Artienne, etc.). Now, when I create a SQL
string with such an apostrophe it gets malformatted (like: "insert into
table1 (Firstname) values ('D'Artienne')" ). So, is there any way to
insert a
name that contains an apostrophe into a table by SQL?


Thanks a lot
peter

User submitted from AEWNET (http://www.aewnet.com/)

 

[User]

What are your opinion/comments/suggestions about the snipet abstracted
below?

I agree with the poster below about "never" doing this.

For a variety of reasons, you have just opened up a major
security hole in your application even if you successfully
convert the apostrophes. You never know which "smart guy"
will start playing with your application just to prove how smart
they are and how dumb you are. You'd be surprised at the
number of "second cousins" that employees let play with
their work software/web sites.

The security hole you open up with dynamic sql strings would
be a cake-walk for any 12 year old who has an ounce of sense
about programming.

Is this the person you want to introduce to your boss as the
one who crashed your app or more likely deleted your data?

My intent is not to hurt your feelings, abuse you, or talk down
to you. Just to give a wake up call to someone I'm sure works
their tail off to build great software. I'd hate to see you get
embarrased.

I understand some applications may need to have different
update or insert statements for different situations. In these
cases, you could include an extra parameter in your
stored procedure that dictates which statements to process.
Include all possible input parameters and ensure that those
that are optional can be set as null or default values if they
aren't passed in.

--
2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
http://www.mastervb.net

Miha,
I wonder the same thing, I guess the answer is that we have not been able
to
come up with a good solution in this space. Do you have any suggestions? I
would be happy to pass them on.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/

Hi Angel,

I hope you have this answer in "favorites answers" as it will have to be
repeated many times
OTOH, I can imagine a scenario where you might have to pass in a dynamically
built WHERE clause instead of parameters:
IN clause is a very tempting candidate - when there is too many parameters.
There are workarounds, but they are all "not that easy" to employ.

As this is very annoying (dealing with parameter lists that is) I can
only
wonder why there is (still) no support for parameter lists even in Yukon.
Can you shed some light on this?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

message
This just cannot be repeated enough times.
DO NOT pass in customer data directly into a Sql Query. ALWAYS use
parameters.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/




You've probably solved this by now. All you have to do is add another
apostrophe beside the one you'd like to store e.g. insert into table
XYZ
(Firstname) values ('D''Artienne').

So in the table the column Firstname stores the value D'Artienne.

Cheers
T


Hi,

in my application I add personal information about customers to a
database
by sending a SQL string. Now, sometimes there are customers with names
that
contain an apostrophe (like D'Artienne, etc.). Now, when I create

a
SQL

string with such an apostrophe it gets malformatted (like: "insert into
table1 (Firstname) values ('D'Artienne')" ). So, is there any way to
insert a
name that contains an apostrophe into a table by SQL?


Thanks a lot
peter

User submitted from AEWNET (http://www.aewnet.com/)