apostrophes in SQL statement

[Rob Meade]

Hi all,

Ok - I kinda new the golden rule, if there's an apostrophe in a bit of text
you wanna write to the SQL server database, just double it up - marvellous -
has been working for ages - nicely!

Today however this appears not to be the case..

I have a page where the user enters text in to a textarea, the info is then
parsed, and the ' replaced to '' - written to the database..

A new chappy trying this today said it was all good but was wondering why
there were '' appearing on the pages - I had a look - sure enough there were
2 apostrophes.

I've logged into this myself this evening and have tried it, it appeared in
the textarea (grabbed back from database) fine - only one - I removed it,
saved it, check it, none on the page, great, then I went back in added 1,
saved it, checked it, and there was only one displayed...

My question is whether or not local/regional settings can affect this - I
personally dont see how, I assumed that this guy was pulling my chain and
actually entering 2 - but that doesn't appear to be the case..

I cant replicate the problem on my test server where I have just tested it
and it was fine - neither does it appear to be a problem for someone else
using this app.

Has anyone got any ideas why it might double it for one person and not
another?

Differences between users were:

me testing - sat on server which is running IIS and SQL Server
other person testing - sat at a PC miles away connecting to my server

Any info appreciated

Regards

Rob

 

[Rob Meade]

...in addtion to my posting..

I just checked the File DSN that I created for this 'copy' of the
application last night - it differed to the previous copy in that I had
ticked "Use regional settings for dates/times/numbers etc" - anyone think
that this could be the cause of the problem?

I couldnt recreate it but then I'm assuming my regional settings sat on the
server are correct...

 

[Rob Meade]

...

most of the characters that caused issues are "escaped" automatically now so
you dont need to double them up

waahhh...

Really, even the apostrophe - when did this happen? Never knew about that -
no one told me....(etc)..

Why then do we still have the problem here, and also at work if we dont
double the apostrophe?

Is this a setting we've missed in SQL Server?

Any info appreciated,

Regards

Rob

 

[Ray at]

Are you using a "safen" function when you insert data? Like:

Function safen(s)
safen = Replace(s, "'", "''")
End Function

And if so, are you inadvertently using it when pulling data OUT of the
database as well?

Post some relevant code snippets if not.

Ray at work

 

[Rob Meade]

Are you using a "safen" function when you insert data? Like:

Function safen(s)
safen = Replace(s, "'", "''")
End Function

I wish I could say yes to that one

Instead its more like:

SQL = Replace(SQL, "'", "'')

each time its used - but the result is the same.

And if so, are you inadvertently using it when pulling data OUT of the
database as well?

Nope - tis fine if I update the pages at my end here, it doesnt happen...

Wondering if its that regional setting thing in the DSN - seems to be the
only difference...

Regards

Rob

 

[Rob Meade]

Woops.... my bad, thought you were in .NET

LOL!

Actually your info is just as useful as we are starting to use .Net at work
now - so that's handy to know...

Regards

Rob

 

[Ray at]

Show code that displays your data.

And what kind of database is it?

Ray at work

 

[Bob Barrows [MVP]]

I avoid this issue entirely by using parameters, but nobody listens to me

 

[Rob Meade]

Show code that displays your data.

Hi Ray, its kinda as I posted above, but here's fuller example - a very
small snippet from a very BIG page - but this is the relevant stuff...

strPageName = Replace(Request.Form("pagename"), "'", "''")
strPageKeywords = Replace(Request.Form("pagekeywords"), "'", "''")
strPageDisplayOrder = Replace(Request.Form("pagedisplayorder"), "'", "''")
strPageContent = Replace(Request.Form("content"), "'", "''")
strPageIsLive = Request.Form("pageislive")
strDisplayEmail = Request.Form("displayemail")
strDisplayPrint = Request.Form("displayprint")

SQLInsert = "EXEC sp_CreateWebPage "
SQLInsert = SQLInsert & "'" & strPageName & "', '" & strPageKeywords &
"', '" & strPageDisplayOrder & "', "
SQLInsert = SQLInsert & "'" & strPageContent & "', '" & strPageIsLive &
"', '0', '0', '0', '0', '0', '0', '0', '', "
SQLInsert = SQLInsert & "'" & strDisplayEmail & "', '" &
strDisplayPrint & "', "
SQLInsert = SQLInsert & "'" & Session("UserID") & "', '" & strSectionID
& "'"

And what kind of database is it?

SQL Server 2000

 

[Ray at]

This is the code to insert. What is the code used to DISPLAY the data?

Ray at work

 

[Ray at]

Touché. [:

Ray at work

 

[Rob Meade]

This is the code to insert. What is the code used to DISPLAY the data?

Hi Ray, sorry, misread your post - thought you were saying "DISPLAY YOUR
CODE" ;o)

SQL = "SELECT PageName, PageContent, PageIsStatic, PageIsKB,
PageIsSearchResults, PageIsNews, DisplayEmail, DisplayPrint "
SQL = SQL & "FROM tblPages "
SQL = SQL & "WHERE PageID = '" & strCurrentPageID & "' AND PageID IN "
SQL = SQL & "(SELECT PageID FROM tblPageAssociations WHERE SectionID IN "
SQL = SQL & "(SELECT SectionID FROM tblSectionAssociations WHERE WebsiteID
= '" & strWebsiteID & "'))"

strPageName = RS("PageName")
strPageContent = RS("PageContent")
strPageIsStatic = RS("PageIsStatic")
strPageIsKB = RS("PageIsKB")
strPageIsSearchResults = RS("PageIsSearchResults")
strPageIsNews = RS("PageIsNews")
strDisplayEmail = RS("DisplayEmail")
strDisplayPrint = RS("DisplayPrint")

These then get thrown into an HTML template - there's no other ASP before
that - I didnt post the next bit as there's a lot of HTML that will just
make a mess (more so) of this post

Rob

 

[Rob Meade]

...

I avoid this issue entirely by using parameters, but nobody listens to me

Small example?



Rob

 

[Ray at]

We'd still need to see a snippet of where you're displaying your data.
You're closer here! But, you know, the part where it's actually
Response.Written or <%=ed...

And if you run this query in query analyzer, do you see '' where you'd exect
to see '?

Ray at work

 

[Rob Meade]

We'd still need to see a snippet of where you're displaying your data.
You're closer here! But, you know, the part where it's actually
Response.Written or <%=ed...

Ok - you asked for it )

<tr>
<td width="1" class="main-outerline"><img
src="<%=strWebsitePath%>images/invis.gif" width="1" height="1"></td>
<td colspan="2" class="main-bodybackground"><font
class="normaltext"><%=strPageContent%></font></td>
<td width="1" class="main-outerline"><img
src="<%=strWebsitePath%>images/invis.gif" width="1" height="1"></td>
</tr>

(a gentle example)

And if you run this query in query analyzer, do you see '' where you'd exect
to see '?

Depends, often I just get a <LONG TEXT> - which is always nice! When I do
see content its fine at my end - I'm convinced that the code is fine - I'm
almost 100% sure it was the DSN setting...the app has been used live for
just over a year now and I've not had any issues with this from the client,
but since setting it up again it arose today when using the different DSN...


Regards

Rob

 

[Ray at]

Okay, cool. The three steps all seem fine, you're right. You have:

strPageContent = Replace(Request.Form("content"), "'", "''")

and

....SQLInsert = SQLInsert & "'" & strPageContent & "',...

and

<td><%=strPageContent%></td>

I see nothing wrong with that. So, what's this about your DSN now? Is
there any reason you're using a DSN? What happens if you switch to the
preferred method of using an OLE DB connection using a connection string
such as:

"Provider=sqloledb;Data Source=YourServer;Initial Catalog=YourDatabase;User
Id=SQLUserID;Password=ThePassword;"

Ray at work

 

[Bob Barrows [MVP]]

...


Small example?



Rob

This part is good:

strPageName = Request.Form("pagename")
strPageKeywords = Request.Form("pagekeywords")
strPageDisplayOrder = Request.Form("pagedisplayorder")
strPageContent = Request.Form("content")
strPageIsLive = Request.Form("pageislive")
strDisplayEmail = Request.Form("displayemail")
strDisplayPrint = Request.Form("displayprint")

Instead of the dynamic sql, call your (badly named*) stored procedure like
this (assumes your connection variable is conn):

conn.sp_CreateWebPage strPageName, _
strPageKeywords, strPageDisplayOrder, _
strPageContent, strPageIsLive, _
"0", "0", "0", "0", "0", "0", "0", _
strDisplayEmail, strDisplayPrint, _
Session("UserID"), strSectionID


If your stored procedure returned a recordset, you can still use this
technique:

set rs=server.createobject("adodb.recordset")
conn.sp_CreateWebPage strPageName, _
strPageKeywords, strPageDisplayOrder, _
strPageContent, strPageIsLive, _
"0", "0", "0", "0", "0", "0", "0", _
strDisplayEmail, strDisplayPrint, _
Session("UserID"), strSectionID, rs

*You should not use the "sp_" prefix when naming your custom stored
procedures. "sp_" should be reserved for system stored procedures, and there
is a performance hit when using that prefix for non-system procedures.


Bob Barrows

 

[Rob Meade]

Okay, cool. The three steps all seem fine, you're right.

Cool )

I see nothing wrong with that.

Marvellous )

So, what's this about your DSN now?

I copied the app to a seperate web on the development server here and
recreated the database, I then remembered that I'd need to create a new DSN
(on the live applications these are not used)...

Is there any reason you're using a DSN?

Old code - and only used currently for the development server, live box used
what you've specified..

When I created the new DSN there were some options as I went through the
'wizard' for the ODBC stuff, one said use Regional settings, seemed like a
good idea so I ticked it - this evening having experienced these problems I
checked the DSN that I use on the 1st copy of this app on the server and it
didn't have that option ticked, so I've removed it from the 2nd copy's DSN
now too.

Either way, locally (me sat on the server here) - it works fine both ways
(as I'd expect) - I'm wondering now if perhaps the ' thing was affected by
regional settings on the users PC that connected to this server earlier
today - thats my only theory on this at this time...

Other than he physically typed in '' to try and fool me (unlikely)...

Regards

Rob

 

[Ray at]

Either way, locally (me sat on the server here) - it works fine both ways
(as I'd expect) - I'm wondering now if perhaps the ' thing was affected by
regional settings on the users PC that connected to this server earlier
today - thats my only theory on this at this time...

I'd drop the DSN altogether, if you think it's suspect. Or, if you have to
use a DSN, recreate it with all the default options.

Regional settings, afaIk, wouldn't affect ' or " characters or anything.

Other than he physically typed in '' to try and fool me (unlikely)...

Well, you may want to be sure by using query analyzer to find out. If you
can't see the data, you could at least do a
SELECT * FROM yourTable where yourColumn LIKE '%''''%'

 

[Dave Anderson]

I avoid this issue entirely by using parameters, but
nobody listens to me

We have a shop rule that requires us to use ADODB.Command objects when
passing parameters from a web request to SQL Server (not to mention
restricting SQL Server access to stored procedures ONLY). This also renders
the issue moot.



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.