ESCAPE the apostrophe

[Thomas Beyerlein]

I have been looking for how to INSERT text with an apostrophe in it into
SQL example:

dim TEXT as string
text = "TEST'S"
INSERT INTO tbl_Test (String) VALUSES('" & TEXT & "')"

This doesn't crash my program but it give me a error and doesn't insert
the string correctly.

Does anyone know the escape char in SQL, i tried &apos, and ESCAPE '/',
with a replace(mystring, "'", "/'")

Any help would be great thanks
Tom

 

[Joergen Bech]

Have you tried escaping it with another ', i.e. something like

....VALUES('" & strText.Replace("'", "''") & "')"

Or use a SqlCommand object and add the strings using
parameter objects without escaping them.

?

/JB

 

[Herfried K. Wagner [MVP]]

I have been looking for how to INSERT text with an apostrophe in it into
SQL example:

dim TEXT as string
text = "TEST'S"
INSERT INTO tbl_Test (String) VALUSES('" & TEXT & "')"

This doesn't crash my program but it give me a error and doesn't insert
the string correctly.

Does anyone know the escape char in SQL, i tried &apos, and ESCAPE '/',
with a replace(mystring, "'", "/'")

Use a parameterized command object instead, which will perform escaping
automatically.

Sample:

<URL:http://www.vb-tips.com/?ID=550279ec-6767-44ff-aaa3-eb8b44af0137>

 

[Jay B. Harlow [MVP - Outlook]]

Thomas,
As Herfried suggests.

Use parameterized command objects! As it avoids SQL injection attacks!

http://msdn.microsoft.com/msdnmag/issues/04/09/SQLInjection/

Of course parameterized command objects also escapes apostrophe's
correctly...

--
Hope this helps
Jay [MVP - Outlook]
..NET Application Architect, Enthusiast, & Evangelist
T.S. Bradley - http://www.tsbradley.net


|
| I have been looking for how to INSERT text with an apostrophe in it into
| SQL example:
|
| dim TEXT as string
| text = "TEST'S"
| INSERT INTO tbl_Test (String) VALUSES('" & TEXT & "')"
|
| This doesn't crash my program but it give me a error and doesn't insert
| the string correctly.
|
| Does anyone know the escape char in SQL, i tried &apos, and ESCAPE '/',
| with a replace(mystring, "'", "/'")
|
| Any help would be great thanks
| Tom
|
|
|

 

[Cor Ligthert [MVP]]

Jay,

As there is one thing that Bill Vaughn agree about (not the only thing) than
is it that this article that you shows should not be on MSDN. For us it is
an invitation for attacks.

As well I have seen often told, is that the parameter does prevent attacks.
In my opinion was there a flauw in SQL server 2000 that was less easy with
parameters and therefore should be used. AFAIK is that now protected by
Servicepack 3.

In the article that you show, I have often tried to find why a parameter
would prevent the attack. I never got it from this article, for me it only
explains how you can do an attack if you have the rights to give a text
command to a SQLserver. A parameter does than protect nothing.

For me the parameter is worthfull because is seamless connects .Net with
databaseservers.

However, maybe can you point me on the part of the article where it shows
that a parameter protects against attacks (not only by telling that).

Cor

 

[Jay B. Harlow [MVP - Outlook]]

Cor,
The section titled "Avoid Dynamic SQL" explains why parameterized SQL
prevents it.

If you need an explanation of how parameterized SQL works verses dynamic
SQL, on the server, you may want to ask "down the hall" in one of the SQL
newsgroups.

--
Hope this helps
Jay [MVP - Outlook]
..NET Application Architect, Enthusiast, & Evangelist
T.S. Bradley - http://www.tsbradley.net


| Jay,
|
| As there is one thing that Bill Vaughn agree about (not the only thing)
than
| is it that this article that you shows should not be on MSDN. For us it
is
| an invitation for attacks.
|
| As well I have seen often told, is that the parameter does prevent
attacks.
| In my opinion was there a flauw in SQL server 2000 that was less easy with
| parameters and therefore should be used. AFAIK is that now protected by
| Servicepack 3.
|
| In the article that you show, I have often tried to find why a parameter
| would prevent the attack. I never got it from this article, for me it only
| explains how you can do an attack if you have the rights to give a text
| command to a SQLserver. A parameter does than protect nothing.
|
| For me the parameter is worthfull because is seamless connects .Net with
| databaseservers.
|
| However, maybe can you point me on the part of the article where it shows
| that a parameter protects against attacks (not only by telling that).
|
| Cor
|
|