Apostrophys in text field - SQL Server as backend

  • Thread starter Thread starter Woody Splawn
  • Start date Start date
W

Woody Splawn

Are there any problems with saving values to a database from a text field
that has an apostrophy in it? I am using SQL Server 2000. My notes are not
clear on this, but I seem to remember that if you save a value to a char or
varchar field in the database and it (the text field) contains an apostropy
in it, it can create problems. Does someone know what I am referring to and
if so what are the issues and where can I find more information?
 
It does... what you need to do is replace all your ' 's with double ''

that will take care of it.
 
Thank you for replying.

If I understand your reply correctly you're saying it does cause problems.
What problem does it create? What is the nature of it. When I save a value
with a comma in it the database seems to accept it ok, I mean, I can
retrieve it OK and the comma is still there.
 
Its only with apostrophe's and strings...

The reason is, when your SQL statements execute (such as an insert) a string
is inserted by

INSERT INTO myTable (myString) Values ('myValue')

you use apostrophies to mark the beginning and end of a string. So, with a
value that has a single apostrophe in it, the SQL server things its the end
or beginning of a string.

therefore, if the apostorphe is in there, it causes SQL to bomb. Commas
don't bomb because it just doesn't matter. They are SQL specific like an
apostrophe. The double apostrophe tells it "yeah, this is an apostrophe but
not the end of mys string, please insert the apostrophe in the string"

and sql does... Kinda like using quotes " " in VB Strings.

HTH.
-CJ
 
I causes all sorts of problems. At best, your query will only fail, at
worst, you'll end up with an injection attack and a hacked database. If you
don't use Command Parameter objects, you'll forget to do the replace and it
will bite you. I've heard many people claim that they'll always remember to
do this, and every time they forget somewhere. The worst part is that since
many people don't have apostrophes in their names, you may not know about
the bug for a long time. Morover, calling Replace every time you create a
SQL Statement is a waste of resources. IN addition, using Parameters will
have a large benefit over hard coded string values. Trust me on this, use
Paramaters (and if at all possible, do yourself a real favor and renounce
dynamic sql construction in exchange for Stored Procs) ...
http://www.knowdotnet.com/articles/storedprocsvb.html

So instead of sql = "SELECT * FROM SomeTable where x = '" & someValue & "'"

Use SELECT * FROM SomeTable where x = @SomeValue"

Then, with your command objects, use cmd.Parameters.Add("@SomeValue",
SqlDbType.Whatever).Value = SomeValue,
it's cleaner, safer, less error prone and faster.

HTH,

Bill
 
I'm sorry, earlier I said something like, "When I save a value with an COMMA
in it, the databasse seems to accept it ok". I said comma, but I meant
apostrophy. That is, let's say I have a textbox with the a value in it of
"Don't say no". When I save it, it seems to save OK and when I retrieve the
record back into the Winform it still says "Don't say no". I don't
understand what the problem is.

I am letting the DataAdapter do the work with regard to saving. That is,
when I create the data adapter I select configure data adapter, and then
advance options, Generate update, insert and delete statements. It creates
the code using @ variables. Does this make a difference? Is this why it
appears to be working? Does the problem manifest itself only when I chose
to update in some other manner or is there something I'm missing?
therefore, if the apostorphe is in there, it causes SQL to bomb.
Click to expand...

When you say it causes SQL to bomb, what specifically happens and when? Are
you saying that if I do an update query under the condidtions you mentioned
that when I try to do the update I will get an exception or error message?
 
Woody Splawn said:
I'm sorry, earlier I said something like, "When I save a value with an COMMA
in it, the databasse seems to accept it ok". I said comma, but I meant
apostrophy. That is, let's say I have a textbox with the a value in it of
"Don't say no". When I save it, it seems to save OK and when I retrieve the
record back into the Winform it still says "Don't say no". I don't
understand what the problem is.

I am letting the DataAdapter do the work with regard to saving. That is,
when I create the data adapter I select configure data adapter, and then
advance options, Generate update, insert and delete statements. It creates
the code using @ variables. Does this make a difference? Is this why it
appears to be working? Does the problem manifest itself only when I chose
to update in some other manner or is there something I'm missing?
Click to expand...

Ok, thats why. Your data adapter alraeady takes care of that stuff for you.
It still generates a SQL statement (because thats just how SQL works) and
replaces values. As stated by william ryan, its always better to use data
adapters and anaything in the .NET framework. So as long as your using DA's
your cool..
When you say it causes SQL to bomb, what specifically happens and when? Are
you saying that if I do an update query under the condidtions you mentioned
that when I try to do the update I will get an exception or error message?
Click to expand...

Usually, you just get like an invalid procedure call or invalid statement
syntax. I've personally never seen any attack resulting from apostrophe's.

and yeah, it will throw an exception, bt your using DA's so you don't have
to worry.
 
Forgive me. I am still somewhat new the Microsoft way of saying and doing
things so I need to ask for clarification.
If you don't use Command Parameter objects
Click to expand...

I think that you are suggesting that when I do an update to a database (like
SQL Server) and I am using VS.net as my front end, to let VS update it with
command parameters as opposed to some other way. Is this right? If so then
I am on OK ground because this is the only way I really know how to do
things. I mean, what I am doing is this.

I am letting the DataAdapter do the work with regard to saving. That is,
when I create the data adapter I select configure DataAdapter, and then
advance options, Generate update, insert and delete statements. It creates
update
code using @ variables. Is this what you mean?

In the few cases where I may update the database in some other way, I can
write some sort of function or procedure that will check the data for
apostrophe's and replace them with double apostrophes. Is this what VS is
doing with command parameters? These times will be few and I can deal with
that but dealing with it on every text field on every form I'll ever create
seemed extremely onerous.
 
Are there any problems with saving values to a database from a text field
that has an apostrophy in it? I am using SQL Server 2000. My notes are not
clear on this, but I seem to remember that if you save a value to a char or
varchar field in the database and it (the text field) contains an apostropy
in it, it can create problems. Does someone know what I am referring to and
if so what are the issues and where can I find more information?
Click to expand...

Nope.. If you're building INSERT statements, replace all single quotes
with two single quotes.

Eg,

Dim szValue As String = "This'll Work Fine"
Dim szSQL = String.Format( _
"INSERT INTO
(Column) VALUES ('{0}')", _
szValue.Replace("'","''")

// CHRIS
 
Hi Woody,

My comment in line.

If you have any concern on this issue, please post here.

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------
From: "Woody Splawn" <[email protected]>
References: <#[email protected]>
Click to expand...
Subject: Re: Apostrophys in text field - SQL Server as backend
Date: Wed, 7 Jan 2004 14:18:38 -0800
Lines: 27
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <O2l#[email protected]>
Newsgroups: microsoft.public.dotnet.languages.vb
NNTP-Posting-Host: 168.158-60-66-fuji-dsl.static.surewest.net 66.60.158.168
Path: cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.
phx.gbl
Xref: cpmsftngxa07.phx.gbl microsoft.public.dotnet.languages.vb:170825
X-Tomcat-NG: microsoft.public.dotnet.languages.vb

Forgive me. I am still somewhat new the Microsoft way of saying and doing
things so I need to ask for clarification.


I think that you are suggesting that when I do an update to a database (like
SQL Server) and I am using VS.net as my front end, to let VS update it with
command parameters as opposed to some other way. Is this right? If so then
I am on OK ground because this is the only way I really know how to do
things. I mean, what I am doing is this.

I am letting the DataAdapter do the work with regard to saving. That is,
when I create the data adapter I select configure DataAdapter, and then
advance options, Generate update, insert and delete statements. It creates
update
code using @ variables. Is this what you mean?
Click to expand...

Yes, I think you may check what the code .NET generate for you in the
"Windows Form Designer generated code" region
In the few cases where I may update the database in some other way, I can
write some sort of function or procedure that will check the data for
apostrophe's and replace them with double apostrophes. Is this what VS is
doing with command parameters? These times will be few and I can deal with
that but dealing with it on every text field on every form I'll ever create
seemed extremely onerous.
Click to expand...
So if you use the method which William suggests .NET will do the convertion
for you.
(i.e. use @SomeValue in your SQL statement)
 
Hi Woody,

Did you have any concern on this issue?
If so please post in the newsgroup.

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Back
Top