apostrophes in SQL statement
Rob Meade
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
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...
Curt_C [MVP]
you dont need to double them up
--
Curt Christianson
Owner/Lead Developer, DF-Software
www.Darkfalz.com
"Rob Meade" <robb....@NO-SPAM.kingswoodweb.net> wrote in message
news:0lyhc.169$lF.17...@news-text.cableinet.net...
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 <%=sLocation%> [MVP]
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" <robb....@NO-SPAM.kingswoodweb.net> wrote in message
news:0lyhc.169$lF.17...@news-text.cableinet.net...
Curt_C [MVP]
--
Curt Christianson
Owner/Lead Developer, DF-Software
www.Darkfalz.com
"Rob Meade" <robb....@NO-SPAM.kingswoodweb.net> wrote in message
news:Hyyhc.186$2W.20...@news-text.cableinet.net...
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 <%=sLocation%> [MVP]
And what kind of database is it?
Ray at work
"Rob Meade" <robb....@NO-SPAM.kingswoodweb.net> wrote in message >
Bob Barrows [MVP]
:-)
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
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 <%=sLocation%> [MVP]
Ray at work
"Rob Meade" <robb....@NO-SPAM.kingswoodweb.net> wrote in message
news:Kizhc.240$Tl1.2...@news-text.cableinet.net...
Ray at <%=sLocation%> [MVP]
Ray at work
"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:%23xnlq98...@TK2MSFTNGP11.phx.gbl...
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 <%=sLocation%> [MVP]
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" <robb....@NO-SPAM.kingswoodweb.net> wrote in message
news:i_zhc.298$2b1.2...@news-text.cableinet.net...
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 :o)
<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 <%=sLocation%> [MVP]
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]
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 :o)
> I see nothing wrong with that.
Marvellous :o)
> 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
A. Nonymous
proplem...
=======
Sub ScanInputString(sInput)
'This function will scan the input string sInput for special characters
(e.g. Chr(39))
'and replace them with the SQL escape-sequence to allow MSSQL Server to
handle the input properly.
'sInput = *in / out* Input string to be scan, updated and returned.
sInput = Replace(sInput, Chr(39), Chr(39) & Chr(39))
End Sub
...
'Import from web page
sProblemDesc = Request.Form("txta_ProblemDesc")
...
'Scan input
Call ScanInputString(sProblemDesc)
'Save to database by calling stored procedure.
conn.Execute("sp_INCIDENT_SAVEPROBLEM '"& sProblemDesc &"')
"Rob Meade" <robb....@NO-SPAM.kingswoodweb.net> wrote in message
news:0lyhc.169$lF.17...@news-text.cableinet.net...
A. Nonymous
"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:#DQILw9J...@TK2MSFTNGP09.phx.gbl...
Ray at <%=sLocation%> [MVP]
> 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.
Bob Barrows [MVP]
> Bob Barrows [MVP] wrote:
>>
>> 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.
Good rule! It's pretty much what I was talking about when I said "using
parameters", with the following provisos:
It's possible to parameterize and run a non-stored-procedure query using the
Command object (although I never do it this way - I'm a big fan of stored
procedures)
It's possible to pass parameters to a stored procedure without using either
dynamic sql or an explicit Command object (classic ASP only) by using the
"stored-procedure-as-connection-method" technique, as I demostrated in my
other post to Robb.
Bob Barrows
Dave Anderson
>
> It's possible to pass parameters to a stored procedure without using
> either dynamic sql or an explicit Command object (classic ASP only)
> by using the "stored-procedure-as-connection-method" technique, as I
> demostrated in my other post to Robb.
Assuming the stored procedure name isn't in the namespace of the connection
methods and properties, I would imagine...
Bob Barrows [MVP]
> Bob Barrows [MVP] wrote:
>>
>> It's possible to pass parameters to a stored procedure without using
>> either dynamic sql or an explicit Command object (classic ASP only)
>> by using the "stored-procedure-as-connection-method" technique, as I
>> demostrated in my other post to Robb.
>
> Assuming the stored procedure name isn't in the namespace of the
> connection methods and properties, I would imagine...
Correct Good point. My naming convention for stored procedures would make
this impossible, but not everyone uses my naming convention ...