apostrophes in SQL statement

Discussion in 'ASP General' started by Rob Meade, Apr 21, 2004.

  1. Rob Meade

    Rob Meade Guest

    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, Apr 21, 2004
    #1
    1. Advertising

  2. Rob Meade

    Rob Meade Guest

    ...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, Apr 21, 2004
    #2
    1. Advertising

  3. Rob Meade

    Rob Meade Guest

    "Curt_C [MVP]" wrote ...

    > 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
     
    Rob Meade, Apr 21, 2004
    #3
  4. Rob Meade

    Ray at Guest

    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" <> wrote in message
    news:0lyhc.169$...
    > 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 ev
     
    Ray at, Apr 21, 2004
    #4
  5. Rob Meade

    Rob Meade Guest

    "Ray at <%=sLocation%> [MVP]" wrote in ...

    > 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, Apr 21, 2004
    #5
  6. Rob Meade

    Rob Meade Guest

    "Curt_C [MVP]" <software_AT_darkfalz.com> wrote in ...

    > 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
     
    Rob Meade, Apr 21, 2004
    #6
  7. Rob Meade

    Ray at Guest

    Show code that displays your data.

    And what kind of database is it?

    Ray at work

    "Rob Meade" <> wrote in message >
    > 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 t
     
    Ray at, Apr 21, 2004
    #7
  8. I avoid this issue entirely by using parameters, but nobody listens to me
    :)
    --
    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.
     
    Bob Barrows [MVP], Apr 21, 2004
    #8
  9. Rob Meade

    Rob Meade Guest

    "Ray at <%=sLocation%> [MVP]" wrote in ...

    > 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
     
    Rob Meade, Apr 21, 2004
    #9
  10. Rob Meade

    Ray at Guest

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

    Ray at work

    "Rob Meade" <> wrote in message
    news:Kizhc.240$...
    > "Ray at <%=sLocation%> [MVP]" wrote in ...
    >
    > > 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, Apr 21, 2004
    #10
  11. Rob Meade

    Ray at Guest

    Touché. [:

    Ray at work

    "Bob Barrows [MVP]" <> wrote in message
    news:%...
    > I avoid this issue entirely by using parameters, but nobody listens to me
    > :)
     
    Ray at, Apr 21, 2004
    #11
  12. Rob Meade

    Rob Meade Guest

    "Ray at <%=sLocation%> [MVP]" wrote...

    > 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, Apr 21, 2004
    #12
  13. Rob Meade

    Rob Meade Guest

    "Bob Barrows [MVP]" wrote ...

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


    Small example?

    :)

    Rob
     
    Rob Meade, Apr 21, 2004
    #13
  14. Rob Meade

    Ray at Guest

    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" <> wrote in message
    news:i_zhc.298$...
    > "Ray at <%=sLocation%> [MVP]" wrote...
    >
    > > 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
    >
    >
    >
     
    Ray at, Apr 21, 2004
    #14
  15. Rob Meade

    Rob Meade Guest

    "Ray at <%=sLocation%> [MVP]" wrote ...

    > 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 :eek:)

    <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
     
    Rob Meade, Apr 21, 2004
    #15
  16. Rob Meade

    Ray at Guest

    "Rob Meade" <> wrote in message
    news:IkAhc.326$...

    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



    >
    > > 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, Apr 21, 2004
    #16
  17. Rob Meade wrote:
    > "Bob Barrows [MVP]" wrote ...
    >
    >> I avoid this issue entirely by using parameters, but nobody listens
    >> to me

    >
    > 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
    --
    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.
     
    Bob Barrows [MVP], Apr 21, 2004
    #17
  18. Rob Meade

    Rob Meade Guest

    "Ray at <%=sLocation%> [MVP]" wrote ...

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


    Cool :eek:)

    > I see nothing wrong with that.


    Marvellous :eek:)

    > 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
     
    Rob Meade, Apr 21, 2004
    #18
  19. Rob Meade

    Ray at Guest

    "Rob Meade" <> wrote in message
    news:XTAhc.356$...

    > 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 '%''''%'
     
    Ray at, Apr 22, 2004
    #19
  20. 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.



    --
    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.
     
    Dave Anderson, Apr 22, 2004
    #20
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Chris Huddle

    SQL and apostrophes

    Chris Huddle, Dec 10, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    762
    Steve C. Orr [MVP, MCSD]
    Dec 10, 2003
  2. Andy Fish

    HtmlEncode with apostrophes

    Andy Fish, Apr 6, 2005, in forum: ASP .Net
    Replies:
    4
    Views:
    8,771
    Nicole Calinoiu
    Apr 6, 2005
  3. darrel

    URLEncode doesn't like apostrophes?

    darrel, Jul 8, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    518
    darrel
    Jul 8, 2005
  4. MS
    Replies:
    3
    Views:
    16,248
    Marcin Grunwald
    Feb 22, 2005
  5. mister-Ed

    Escaping apostrophes inserting into sql

    mister-Ed, Oct 5, 2007, in forum: ASP .Net
    Replies:
    1
    Views:
    401
    David Wier
    Oct 5, 2007
Loading...

Share This Page