escape single quote in MS SQL in .net framework

[angus]

Dear All,

Are there any method provided in .net framework(ado.net) that can escape
single quote in MS SQL.

For example

If i want to update the field in myTable in MS SQL to a string, like,
o'clock

i have to convert the o'clock to o''clock

therefore the insert statement is as follow:

Dim value As String = "o'clock"
sqlStatement = "update myTable set (field) values (" &
escapequotefunction(value) & ")"

..............

is there any similar method in .net that implemented escapequotefunction()
?


Regards,
Angus

 

[Miha Markic [MVP C#]]

Angus,

You might use string.Replace("'", "''") method.
However you should use parameters instead of creating the sql command.
See the
Using Parameters with a DataAdapter
..net help topic.

 

[angus]

Besides single quote, what else i should escape before entering to the MS
SQL?

Angus,

You might use string.Replace("'", "''") method.
However you should use parameters instead of creating the sql command.
See the
Using Parameters with a DataAdapter
.net help topic.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Dear All,

Are there any method provided in .net framework(ado.net) that can escape
single quote in MS SQL.

For example

If i want to update the field in myTable in MS SQL to a string, like,
o'clock

i have to convert the o'clock to o''clock

therefore the insert statement is as follow:

Dim value As String = "o'clock"
sqlStatement = "update myTable set (field) values (" &
escapequotefunction(value) & ")"

.............

is there any similar method in .net that implemented escapequotefunction()
?


Regards,
Angus

 

[angus]

However you should use parameters instead of creating the sql command.

it is a difficult story for me to use parameters.

As my backend db can be MS SQL, oracle, mysql, ........etc

therefore, i cannot do something like

cmd.Parameters.add("@field", SqlDbType.nvarchar, "field_name")

as sqldbtype is only for sql server.

Thank you.

Angus,

You might use string.Replace("'", "''") method.
However you should use parameters instead of creating the sql command.
See the
Using Parameters with a DataAdapter
.net help topic.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Dear All,

Are there any method provided in .net framework(ado.net) that can escape
single quote in MS SQL.

For example

If i want to update the field in myTable in MS SQL to a string, like,
o'clock

i have to convert the o'clock to o''clock

therefore the insert statement is as follow:

Dim value As String = "o'clock"
sqlStatement = "update myTable set (field) values (" &
escapequotefunction(value) & ")"

.............

is there any similar method in .net that implemented escapequotefunction()
?


Regards,
Angus

 

[Eric]

You should be using OLEDB then; that way you can connect to any database and
still use parameters.

Without parameters, you are usually forcing teh db to find an execution plan
every time the statement occurs. On large tables this will kill performance
and scalability.

I connect to SQLServer and Oracle without any select case still using
params. Let me know if you need an example.

it is a difficult story for me to use parameters.

As my backend db can be MS SQL, oracle, mysql, ........etc

therefore, i cannot do something like

cmd.Parameters.add("@field", SqlDbType.nvarchar, "field_name")

as sqldbtype is only for sql server.

Thank you.

Angus,

You might use string.Replace("'", "''") method.
However you should use parameters instead of creating the sql command.
See the
Using Parameters with a DataAdapter
.net help topic.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Dear All,

Are there any method provided in .net framework(ado.net) that can escape
single quote in MS SQL.

For example

If i want to update the field in myTable in MS SQL to a string, like,
o'clock

i have to convert the o'clock to o''clock

therefore the insert statement is as follow:

Dim value As String = "o'clock"
sqlStatement = "update myTable set (field) values (" &
escapequotefunction(value) & ")"

.............

is there any similar method in .net that implemented escapequotefunction()
?


Regards,
Angus

 

[William Ryan eMVP]

Angus:

I say this respectfully, but it's a lot more difficult to not use them. As
usual, I'm in total agreement with Miha on this. Besies No one ever lost a
db due to an injection attack using Parameterized queries. There are a
whole lot of other problems you can avoid too, the least of which is the
single quote issue. Do you really want to call replace every single place
you have a varchar/char field that could have an apostrophe or single quote?
Do you really want to forego the benefits of cached execution plans? Even
things that run fine today may grow into tables that queries don't perform
well against in the future.

You could use a Factory pattern to gen those parameters so you can
accomodate multiple back end datasources with relative ease. If you have
multiple back ends you'll have to write some different logic anyway for the
different providers unless you also want to forego the performance and
feature benefits of using db specific providers.

I guess my point is that not doing it isn't free by any means, and in all
likelihood you're going to forget to escape something at some point. If you
do then you probably won't know of the bug until a user tells you about it.
That will entail fixing the code and redistributing it or redeploying it.
If you get hacked it'll be even worse.

Just my two cents.

Bill


www.devbuzz.com
www.knowdotnet.com

it is a difficult story for me to use parameters.

As my backend db can be MS SQL, oracle, mysql, ........etc

therefore, i cannot do something like

cmd.Parameters.add("@field", SqlDbType.nvarchar, "field_name")

as sqldbtype is only for sql server.

Thank you.

Angus,

You might use string.Replace("'", "''") method.
However you should use parameters instead of creating the sql command.
See the
Using Parameters with a DataAdapter
.net help topic.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Dear All,

Are there any method provided in .net framework(ado.net) that can escape
single quote in MS SQL.

For example

If i want to update the field in myTable in MS SQL to a string, like,
o'clock

i have to convert the o'clock to o''clock

therefore the insert statement is as follow:

Dim value As String = "o'clock"
sqlStatement = "update myTable set (field) values (" &
escapequotefunction(value) & ")"

.............

is there any similar method in .net that implemented escapequotefunction()
?


Regards,
Angus

 

[angus]

Thank you for your information.

Indeed, it is my project manager who decided to write the backend in that
way.

I also think that it is not a good way.

Perhaps, can you give me some articles on the follow matters? Thank you

Do you really want to forego the benefits of cached execution plans?
There are a whole lot of other problems you can avoid too

Angus:

I say this respectfully, but it's a lot more difficult to not use them. As
usual, I'm in total agreement with Miha on this. Besies No one ever lost a
db due to an injection attack using Parameterized queries. There are a
whole lot of other problems you can avoid too, the least of which is the
single quote issue. Do you really want to call replace every single place
you have a varchar/char field that could have an apostrophe or single quote?
Do you really want to forego the benefits of cached execution plans? Even
things that run fine today may grow into tables that queries don't perform
well against in the future.

You could use a Factory pattern to gen those parameters so you can
accomodate multiple back end datasources with relative ease. If you have
multiple back ends you'll have to write some different logic anyway for the
different providers unless you also want to forego the performance and
feature benefits of using db specific providers.

I guess my point is that not doing it isn't free by any means, and in all
likelihood you're going to forget to escape something at some point. If you
do then you probably won't know of the bug until a user tells you about it.
That will entail fixing the code and redistributing it or redeploying it.
If you get hacked it'll be even worse.

Just my two cents.

Bill


www.devbuzz.com
www.knowdotnet.com

However you should use parameters instead of creating the sql command.

it is a difficult story for me to use parameters.

As my backend db can be MS SQL, oracle, mysql, ........etc

therefore, i cannot do something like

cmd.Parameters.add("@field", SqlDbType.nvarchar, "field_name")

as sqldbtype is only for sql server.

Thank you.

Angus,

You might use string.Replace("'", "''") method.
However you should use parameters instead of creating the sql command.
See the
Using Parameters with a DataAdapter
.net help topic.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Dear All,

Are there any method provided in .net framework(ado.net) that can escape
single quote in MS SQL.

For example

If i want to update the field in myTable in MS SQL to a string, like,
o'clock

i have to convert the o'clock to o''clock

therefore the insert statement is as follow:

Dim value As String = "o'clock"
sqlStatement = "update myTable set (field) values (" &
escapequotefunction(value) & ")"

.............

is there any similar method in .net that implemented escapequotefunction()
?


Regards,
Angus

 

[angus]

Without parameters, you are usually forcing teh db to find an execution
plan
Where can i find those information? Thank you for your advice

You should be using OLEDB then; that way you can connect to any database and
still use parameters.

Without parameters, you are usually forcing teh db to find an execution plan
every time the statement occurs. On large tables this will kill performance
and scalability.

I connect to SQLServer and Oracle without any select case still using
params. Let me know if you need an example.

However you should use parameters instead of creating the sql command.

it is a difficult story for me to use parameters.

As my backend db can be MS SQL, oracle, mysql, ........etc

therefore, i cannot do something like

cmd.Parameters.add("@field", SqlDbType.nvarchar, "field_name")

as sqldbtype is only for sql server.

Thank you.

Angus,

You might use string.Replace("'", "''") method.
However you should use parameters instead of creating the sql command.
See the
Using Parameters with a DataAdapter
.net help topic.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Dear All,

Are there any method provided in .net framework(ado.net) that can escape
single quote in MS SQL.

For example

If i want to update the field in myTable in MS SQL to a string, like,
o'clock

i have to convert the o'clock to o''clock

therefore the insert statement is as follow:

Dim value As String = "o'clock"
sqlStatement = "update myTable set (field) values (" &
escapequotefunction(value) & ")"

.............

is there any similar method in .net that implemented escapequotefunction()
?


Regards,
Angus

 

[William Ryan eMVP]

http://www.c-sharpcorner.com/Code/2002/July/GenericDataAccessCompActivator.asp
http://www.c-sharpcorner.com/Code/2002/July/GenericDataProvider.asp
http://abstractadonet.sourceforge.net/

HTH,

Bill

www.devbuzz.com
www.knowdotnet.com

Thank you for your information.

Indeed, it is my project manager who decided to write the backend in that
way.

I also think that it is not a good way.

Perhaps, can you give me some articles on the follow matters? Thank you

Do you really want to forego the benefits of cached execution plans?
There are a whole lot of other problems you can avoid too

Angus:

I say this respectfully, but it's a lot more difficult to not use them. As
usual, I'm in total agreement with Miha on this. Besies No one ever

lost

a
db due to an injection attack using Parameterized queries. There are a
whole lot of other problems you can avoid too, the least of which is the
single quote issue. Do you really want to call replace every single place
you have a varchar/char field that could have an apostrophe or single quote?
Do you really want to forego the benefits of cached execution plans? Even
things that run fine today may grow into tables that queries don't perform
well against in the future.

You could use a Factory pattern to gen those parameters so you can
accomodate multiple back end datasources with relative ease. If you have
multiple back ends you'll have to write some different logic anyway for the
different providers unless you also want to forego the performance and
feature benefits of using db specific providers.

I guess my point is that not doing it isn't free by any means, and in all
likelihood you're going to forget to escape something at some point. If you
do then you probably won't know of the bug until a user tells you about it.
That will entail fixing the code and redistributing it or redeploying it.
If you get hacked it'll be even worse.

Just my two cents.

Bill


www.devbuzz.com
www.knowdotnet.com

However you should use parameters instead of creating the sql command.

it is a difficult story for me to use parameters.

As my backend db can be MS SQL, oracle, mysql, ........etc

therefore, i cannot do something like

cmd.Parameters.add("@field", SqlDbType.nvarchar, "field_name")

as sqldbtype is only for sql server.

Thank you.


"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
Angus,

You might use string.Replace("'", "''") method.
However you should use parameters instead of creating the sql command.
See the
Using Parameters with a DataAdapter
.net help topic.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Dear All,

Are there any method provided in .net framework(ado.net) that can escape
single quote in MS SQL.

For example

If i want to update the field in myTable in MS SQL to a string, like,
o'clock

i have to convert the o'clock to o''clock

therefore the insert statement is as follow:

Dim value As String = "o'clock"
sqlStatement = "update myTable set (field) values (" &
escapequotefunction(value) & ")"

.............

is there any similar method in .net that implemented
escapequotefunction()
?


Regards,
Angus

 

[angus]

really thanks a lotttttttttttttttttttttttttttttttt

http://www.c-sharpcorner.com/Code/2002/July/GenericDataAccessCompActivator.asp
http://www.c-sharpcorner.com/Code/2002/July/GenericDataProvider.asp
http://abstractadonet.sourceforge.net/

HTH,

Bill

www.devbuzz.com
www.knowdotnet.com

Thank you for your information.

Indeed, it is my project manager who decided to write the backend in that
way.

I also think that it is not a good way.

Perhaps, can you give me some articles on the follow matters? Thank you

Do you really want to forego the benefits of cached execution plans?
There are a whole lot of other problems you can avoid too

Angus:

I say this respectfully, but it's a lot more difficult to not use

them.
As

usual, I'm in total agreement with Miha on this. Besies No one ever

lost

a
db due to an injection attack using Parameterized queries. There are a
whole lot of other problems you can avoid too, the least of which is the
single quote issue. Do you really want to call replace every single place
you have a varchar/char field that could have an apostrophe or single quote?
Do you really want to forego the benefits of cached execution plans? Even
things that run fine today may grow into tables that queries don't perform
well against in the future.

You could use a Factory pattern to gen those parameters so you can
accomodate multiple back end datasources with relative ease. If you have
multiple back ends you'll have to write some different logic anyway

for
the

different providers unless you also want to forego the performance and
feature benefits of using db specific providers.

I guess my point is that not doing it isn't free by any means, and in all
likelihood you're going to forget to escape something at some point.

If
you

do then you probably won't know of the bug until a user tells you

about
it.

That will entail fixing the code and redistributing it or redeploying it.
If you get hacked it'll be even worse.

Just my two cents.

Bill


www.devbuzz.com
www.knowdotnet.com
However you should use parameters instead of creating the sql command.

it is a difficult story for me to use parameters.

As my backend db can be MS SQL, oracle, mysql, ........etc

therefore, i cannot do something like

cmd.Parameters.add("@field", SqlDbType.nvarchar, "field_name")

as sqldbtype is only for sql server.

Thank you.


"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
Angus,

You might use string.Replace("'", "''") method.
However you should use parameters instead of creating the sql command.
See the
Using Parameters with a DataAdapter
.net help topic.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Dear All,

Are there any method provided in .net framework(ado.net) that can
escape
single quote in MS SQL.

For example

If i want to update the field in myTable in MS SQL to a string, like,
o'clock

i have to convert the o'clock to o''clock

therefore the insert statement is as follow:

Dim value As String = "o'clock"
sqlStatement = "update myTable set (field) values (" &
escapequotefunction(value) & ")"

.............

is there any similar method in .net that implemented
escapequotefunction()
?


Regards,
Angus

 

[William Ryan eMVP]

No problem man. I've done this a few times so if you get stuck, let me
know.

Cheers,

Bill

www.devbuzz.com
www.knowdotnet.com

really thanks a lotttttttttttttttttttttttttttttttt

http://www.c-sharpcorner.com/Code/2002/July/GenericDataAccessCompActivator.asp

http://www.c-sharpcorner.com/Code/2002/July/GenericDataProvider.asp
http://abstractadonet.sourceforge.net/

HTH,

Bill

www.devbuzz.com
www.knowdotnet.com

ever
lost

are

a

whole lot of other problems you can avoid too, the least of which is the
single quote issue. Do you really want to call replace every single place
you have a varchar/char field that could have an apostrophe or single
quote?
Do you really want to forego the benefits of cached execution plans? Even
things that run fine today may grow into tables that queries don't perform
well against in the future.

You could use a Factory pattern to gen those parameters so you can
accomodate multiple back end datasources with relative ease. If you have
multiple back ends you'll have to write some different logic anyway for
the
different providers unless you also want to forego the performance and
feature benefits of using db specific providers.

I guess my point is that not doing it isn't free by any means, and

in
all

likelihood you're going to forget to escape something at some point. If
you
do then you probably won't know of the bug until a user tells you about
it.
That will entail fixing the code and redistributing it or

redeploying
it.

If you get hacked it'll be even worse.

Just my two cents.

Bill


www.devbuzz.com
www.knowdotnet.com
However you should use parameters instead of creating the sql command.

it is a difficult story for me to use parameters.

As my backend db can be MS SQL, oracle, mysql, ........etc

therefore, i cannot do something like

cmd.Parameters.add("@field", SqlDbType.nvarchar, "field_name")

as sqldbtype is only for sql server.

Thank you.


"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
Angus,

You might use string.Replace("'", "''") method.
However you should use parameters instead of creating the sql command.
See the
Using Parameters with a DataAdapter
.net help topic.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software
development
miha at rthand com
www.rthand.com

Dear All,

Are there any method provided in .net framework(ado.net) that can
escape
single quote in MS SQL.

For example

If i want to update the field in myTable in MS SQL to a string,
like,
o'clock

i have to convert the o'clock to o''clock

therefore the insert statement is as follow:

Dim value As String = "o'clock"
sqlStatement = "update myTable set (field) values (" &
escapequotefunction(value) & ")"

.............

is there any similar method in .net that implemented
escapequotefunction()
?


Regards,
Angus