Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    18

    Unanswered: Trouble with Single Quotes

    Hello Everyone!

    I am having a problem with updating a password within a table. Below is the old and new password that needs to be updated.
    Old2"\3("-"3#@(%P="''_\DD@D\*
    New :"I+)#]*1#E,%R-''7^,TEA$X&^O_-68DIK,@

    Below is the query that I am trying to run. I believe my problem is becuase of the Single Quotes found within both the old and new password.

    I have tried to escape the single quotes, @local_variable, CHAR(39) and other ways of playing with the string. I cannot seem to find a solution.

    If you were to ignore the update statement, the SELECT statement would return no results even though the PRINT statement displays the required data.
    This is what baffles me and why I cannot run the UPDATE statement, becuase of course, nothing would be updated since no results were returned.

    Can anyone offer their assistance?

    Thank you in advance.

    __________________________________________________ _______________

    DECLARE
    @OLD_PASSWORD VARCHAR(50),
    @NEW_PASSWORD VARCHAR(50)

    -- 2"\3("-"3#@(%P="''_\DD@D\* ///// The old password.

    SET @OLD_PASSWORD = '2"\3("-"3#@(%P="' + CHAR(39) + CHAR(39) + '_\DD@D\*'

    -- :"I+)#]*1#E,%R-''7^,TEA$X&^O_-68DIK,@ ///// The new password.

    SET @NEW_PASSWORD = ':"I+)#]*1#E,%R-' + CHAR(39) + CHAR(39) + '7^,TEA$X&^O_-68DIK,@'

    PRINT @OLD_PASSWORD
    PRINT @NEW_PASSWORD

    SELECT *
    FROM Credentials
    WHERE
    Is_Active = 1 AND
    [Password] = @OLD_PASSWORD

    /* Attempt to update the password table. */
    UPDATE Credentials
    SET [Password] = @NEW_PASSWORD
    WHERE [ID] IN
    (SELECT [ID]
    FROM Credentials
    WHERE
    Is_Active = 1 AND
    [Password] = @OLD_PASSWORD )

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    When using single quotation marks to delimit a character constant that contains an embedded single quotation mark, use two single quotation marks to represent the embedded single quotation mark, for example:

    SET @MyCharVar = 'O''Leary'

    (Books Online)
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    May 2004
    Posts
    18
    Thank you for your reply.

    I am aware of escaping single quotes. I did do that and it still did not return any record sets.

    I was able to get what I wanted however but through a completely different way. Below is what was done.

    UPDATE CREDENTIALS
    SET PASSWORD = (SELECT PASSWORD FROM CREDENTIALS WHERE ID = 383),
    DTSTAMP_ADDED = GetDate()
    WHERE [PASSWORD] = (SELECT PASSWORD FROM CREDENTIALS WHERE ID = 93) AND IS_ACTIVE = 1

    Somone had to get me to think outside of the box for this one.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •