Problem with building sql statement to handle single quote in a fi

Discussion in 'ASP General' started by Jack, Jan 10, 2006.

  1. Jack

    Jack Guest

    Hi, I have a asp page where part of the code is as follows. This builds up
    the sql statement partially.

    sql01 = "UPDATE EquipmentTbl SET "
    sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "', "
    sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
    sql01 = sql01 & "Location = '" & request.form(strLoc) & "', "



    RESULT OF PARTIAL UPDATE STATEMENT USING RESPONSE.WRITE IS:

    UPDATE EquipmentTbl SET SerialNumber = 'A83737', Description = 'Video
    Conferencing Equipment', Location = 'Conference Rooms cabinet',



    Now, in the above, I would like to be able to put the location field as
    'Don's room'. In other words,
    I would like to handle the aprostrophe after Don.
    With this in mind I am changing the code as following:


    'Old ones in data base go through here
    sql01 = "UPDATE EquipmentTbl SET "
    sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "', "
    sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
    sql01 = sql01 & "Location = '" & Replace((request.form(strLoc), "'",
    "''") & "', "


    However, when generating this sql partial sql statement, I am getting an
    error as follows:
    I guess I am not getting the hang of the syntax for handling the single
    quote in the sql statement.



    /gwisbrandnewready6mod2/WriteEquipmentExpLines.asp, line 243, column 63
    sql01 = sql01 & "Location = '" & Replace((request.form(strLoc), "'", "''") &
    "', "
    --------------------------------------------------------------^

    Any help is appreciated. Thanks in advance.
    Jack, Jan 10, 2006
    #1
    1. Advertising

  2. Jack wrote:
    > Hi, I have a asp page where part of the code is as follows. This
    > builds up the sql statement partially.
    >
    > sql01 = "UPDATE EquipmentTbl SET "
    > sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "',
    > " sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
    > sql01 = sql01 & "Location = '" & request.form(strLoc) & "', "
    >
    >
    >
    > RESULT OF PARTIAL UPDATE STATEMENT USING RESPONSE.WRITE IS:
    >
    > UPDATE EquipmentTbl SET SerialNumber = 'A83737', Description = 'Video
    > Conferencing Equipment', Location = 'Conference Rooms cabinet',
    >
    >
    >
    > Now, in the above, I would like to be able to put the location field
    > as 'Don's room'. In other words,
    > I would like to handle the aprostrophe after Don.
    > With this in mind I am changing the code as following:
    >
    >
    > 'Old ones in data base go through here
    > sql01 = "UPDATE EquipmentTbl SET "
    > sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "',
    > " sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
    > sql01 = sql01 & "Location = '" & Replace((request.form(strLoc), "'",


    Is this line break deliberate?
    The whole Replace statement should be on a single line ...

    > "''") & "', "
    >
    >
    > However, when generating this sql partial sql statement, I am getting
    > an error as follows:
    > I guess I am not getting the hang of the syntax for handling the
    > single quote in the sql statement.
    >
    >
    >
    > /gwisbrandnewready6mod2/WriteEquipmentExpLines.asp, line 243, column
    > 63 sql01 = sql01 & "Location = '" & Replace((request.form(strLoc),
    > "'", "''") & "', "
    > --------------------------------------------------------------^
    >

    If you used parameters, this would not be a problem. Try this:

    sql01 = "UPDATE EquipmentTbl SET "
    sql01 = sql01 & "SerialNumber = ?,"
    sql01 = sql01 & "Description = ?,"
    sql01 = sql01 & "Location = ?"
    sql01 = sql01 & "WHERE ..."

    arParms=Array(request.form(strSerialNum), _
    request.form(strDesc),request.form(strLoc))
    dim cmd
    set cmd=createobject("adodb.command")
    set cmd.activeconnection=objConn
    cmd.CommandText=sql01
    cmd.commandType=1 'adCmdText
    cmd.Execute ,arParms,128 'adExecuteNoRecords

    See? No worries about delimiters or apostrophes (or sql injection).

    However:

    sql01 = "UPDATE EquipmentTbl SET "
    sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "', "
    sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
    sql01 = sql01 & "Location = '" & request.form(strLoc) & "', "
    sql01 = sql01 & Replace(request.form(strLoc),"'","''") & "', "


    --
    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], Jan 10, 2006
    #2
    1. Advertising

  3. Bob Barrows [MVP] wrote:
    >
    > sql01 = "UPDATE EquipmentTbl SET "
    > sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "',
    > " sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
    > sql01 = sql01 & "Location = '" & request.form(strLoc) & "', "
    > sql01 = sql01 & Replace(request.form(strLoc),"'","''") & "', "

    Oops. It should be:

    sql01 = "UPDATE EquipmentTbl SET "
    sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "', "
    sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
    sql01 = sql01 & "Location = '"
    sql01 = sql01 & Replace(request.form(strLoc),"'","''") & "', "


    --
    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], Jan 10, 2006
    #3
  4. Jack

    Jack Guest

    Re: Problem with building sql statement to handle single quote in

    Well, I guess there was some communication gap. It works fine now. Thanks a
    lot. Best Regards.

    "Jack" wrote:

    > Thanks Bob for your generous help. This application has been developed by
    > someone who worked before me for long time. In order to change all the sql
    > code, it would take quite a bit of time which I do not have. However, for new
    > projects I have started applying your concepts and it works real well
    > avoiding the pitalls you have described. Coming back to the sql statment
    > change you recommended i.e.
    > sql01 = "UPDATE EquipmentTbl SET "
    > sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "', "
    > sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
    > sql01 = sql01 & "Location = '" & request.form(strLoc) & "', "
    > sql01 = sql01 & Replace(request.form(strLoc),"'","''") & "', "
    >
    > I am now getting the following sql generated by ther response.wrtie statement:
    >
    > UPDATE EquipmentTbl SET SerialNumber = 'A83737', Description = 'Video
    > Conferencing Equipment', Location = 'Conference Room's cabinet',Conference
    > Room''s cabinet',
    >
    > Here the location = is giving two different values, one with single quote
    > and the other with double. How do I change the code so that the generated sql
    > statement would be:
    >
    > UPDATE EquipmentTbl SET SerialNumber = 'A83737', Description = 'Video
    > Conferencing Equipment', Location = 'Conference Room''s cabinet',
    >
    > Thanks again for any help in advance.
    >
    >
    >
    > "Bob Barrows [MVP]" wrote:
    >
    > > Jack wrote:
    > > > Hi, I have a asp page where part of the code is as follows. This
    > > > builds up the sql statement partially.
    > > >
    > > > sql01 = "UPDATE EquipmentTbl SET "
    > > > sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "',
    > > > " sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
    > > > sql01 = sql01 & "Location = '" & request.form(strLoc) & "', "
    > > >
    > > >
    > > >
    > > > RESULT OF PARTIAL UPDATE STATEMENT USING RESPONSE.WRITE IS:
    > > >
    > > > UPDATE EquipmentTbl SET SerialNumber = 'A83737', Description = 'Video
    > > > Conferencing Equipment', Location = 'Conference Rooms cabinet',
    > > >
    > > >
    > > >
    > > > Now, in the above, I would like to be able to put the location field
    > > > as 'Don's room'. In other words,
    > > > I would like to handle the aprostrophe after Don.
    > > > With this in mind I am changing the code as following:
    > > >
    > > >
    > > > 'Old ones in data base go through here
    > > > sql01 = "UPDATE EquipmentTbl SET "
    > > > sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "',
    > > > " sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
    > > > sql01 = sql01 & "Location = '" & Replace((request.form(strLoc), "'",

    > >
    > > Is this line break deliberate?
    > > The whole Replace statement should be on a single line ...
    > >
    > > > "''") & "', "
    > > >
    > > >
    > > > However, when generating this sql partial sql statement, I am getting
    > > > an error as follows:
    > > > I guess I am not getting the hang of the syntax for handling the
    > > > single quote in the sql statement.
    > > >
    > > >
    > > >
    > > > /gwisbrandnewready6mod2/WriteEquipmentExpLines.asp, line 243, column
    > > > 63 sql01 = sql01 & "Location = '" & Replace((request.form(strLoc),
    > > > "'", "''") & "', "
    > > > --------------------------------------------------------------^
    > > >

    > > If you used parameters, this would not be a problem. Try this:
    > >
    > > sql01 = "UPDATE EquipmentTbl SET "
    > > sql01 = sql01 & "SerialNumber = ?,"
    > > sql01 = sql01 & "Description = ?,"
    > > sql01 = sql01 & "Location = ?"
    > > sql01 = sql01 & "WHERE ..."
    > >
    > > arParms=Array(request.form(strSerialNum), _
    > > request.form(strDesc),request.form(strLoc))
    > > dim cmd
    > > set cmd=createobject("adodb.command")
    > > set cmd.activeconnection=objConn
    > > cmd.CommandText=sql01
    > > cmd.commandType=1 'adCmdText
    > > cmd.Execute ,arParms,128 'adExecuteNoRecords
    > >
    > > See? No worries about delimiters or apostrophes (or sql injection).
    > >
    > > However:
    > >
    > > sql01 = "UPDATE EquipmentTbl SET "
    > > sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "', "
    > > sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
    > > sql01 = sql01 & "Location = '" & request.form(strLoc) & "', "
    > > sql01 = sql01 & Replace(request.form(strLoc),"'","''") & "', "
    > >
    > >
    > > --
    > > 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.
    > >
    > >
    > >
    Jack, Jan 10, 2006
    #4
  5. Jack

    Jack Guest

    Re: Problem with building sql statement to handle single quote in

    Thanks Bob for your generous help. This application has been developed by
    someone who worked before me for long time. In order to change all the sql
    code, it would take quite a bit of time which I do not have. However, for new
    projects I have started applying your concepts and it works real well
    avoiding the pitalls you have described. Coming back to the sql statment
    change you recommended i.e.
    sql01 = "UPDATE EquipmentTbl SET "
    sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "', "
    sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
    sql01 = sql01 & "Location = '" & request.form(strLoc) & "', "
    sql01 = sql01 & Replace(request.form(strLoc),"'","''") & "', "

    I am now getting the following sql generated by ther response.wrtie statement:

    UPDATE EquipmentTbl SET SerialNumber = 'A83737', Description = 'Video
    Conferencing Equipment', Location = 'Conference Room's cabinet',Conference
    Room''s cabinet',

    Here the location = is giving two different values, one with single quote
    and the other with double. How do I change the code so that the generated sql
    statement would be:

    UPDATE EquipmentTbl SET SerialNumber = 'A83737', Description = 'Video
    Conferencing Equipment', Location = 'Conference Room''s cabinet',

    Thanks again for any help in advance.



    "Bob Barrows [MVP]" wrote:

    > Jack wrote:
    > > Hi, I have a asp page where part of the code is as follows. This
    > > builds up the sql statement partially.
    > >
    > > sql01 = "UPDATE EquipmentTbl SET "
    > > sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "',
    > > " sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
    > > sql01 = sql01 & "Location = '" & request.form(strLoc) & "', "
    > >
    > >
    > >
    > > RESULT OF PARTIAL UPDATE STATEMENT USING RESPONSE.WRITE IS:
    > >
    > > UPDATE EquipmentTbl SET SerialNumber = 'A83737', Description = 'Video
    > > Conferencing Equipment', Location = 'Conference Rooms cabinet',
    > >
    > >
    > >
    > > Now, in the above, I would like to be able to put the location field
    > > as 'Don's room'. In other words,
    > > I would like to handle the aprostrophe after Don.
    > > With this in mind I am changing the code as following:
    > >
    > >
    > > 'Old ones in data base go through here
    > > sql01 = "UPDATE EquipmentTbl SET "
    > > sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "',
    > > " sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
    > > sql01 = sql01 & "Location = '" & Replace((request.form(strLoc), "'",

    >
    > Is this line break deliberate?
    > The whole Replace statement should be on a single line ...
    >
    > > "''") & "', "
    > >
    > >
    > > However, when generating this sql partial sql statement, I am getting
    > > an error as follows:
    > > I guess I am not getting the hang of the syntax for handling the
    > > single quote in the sql statement.
    > >
    > >
    > >
    > > /gwisbrandnewready6mod2/WriteEquipmentExpLines.asp, line 243, column
    > > 63 sql01 = sql01 & "Location = '" & Replace((request.form(strLoc),
    > > "'", "''") & "', "
    > > --------------------------------------------------------------^
    > >

    > If you used parameters, this would not be a problem. Try this:
    >
    > sql01 = "UPDATE EquipmentTbl SET "
    > sql01 = sql01 & "SerialNumber = ?,"
    > sql01 = sql01 & "Description = ?,"
    > sql01 = sql01 & "Location = ?"
    > sql01 = sql01 & "WHERE ..."
    >
    > arParms=Array(request.form(strSerialNum), _
    > request.form(strDesc),request.form(strLoc))
    > dim cmd
    > set cmd=createobject("adodb.command")
    > set cmd.activeconnection=objConn
    > cmd.CommandText=sql01
    > cmd.commandType=1 'adCmdText
    > cmd.Execute ,arParms,128 'adExecuteNoRecords
    >
    > See? No worries about delimiters or apostrophes (or sql injection).
    >
    > However:
    >
    > sql01 = "UPDATE EquipmentTbl SET "
    > sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "', "
    > sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
    > sql01 = sql01 & "Location = '" & request.form(strLoc) & "', "
    > sql01 = sql01 & Replace(request.form(strLoc),"'","''") & "', "
    >
    >
    > --
    > 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.
    >
    >
    >
    Jack, Jan 10, 2006
    #5
    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. Replies:
    3
    Views:
    13,128
  2. Eric Layman
    Replies:
    3
    Views:
    611
    Rad [Visual C# MVP]
    Apr 14, 2007
  3. Evan
    Replies:
    1
    Views:
    380
    Marc 'BlackJack' Rintsch
    Nov 4, 2008
  4. Evan
    Replies:
    3
    Views:
    503
    Marc 'BlackJack' Rintsch
    Nov 4, 2008
  5. vikrant
    Replies:
    8
    Views:
    340
    vikrant
    May 17, 2007
Loading...

Share This Page