"Unclosed quote character mark before.." on updating crypted password to SQl
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 8 of 8

Thread: "Unclosed quote character mark before.." on updating crypted password to SQl

  1. #1
    Join Date
    Jul 2012
    Posts
    6

    "Unclosed quote character mark before.." on updating crypted password to SQl

    hi guys,i'm new in this forums hope it can help me to solve my problem..
    anyway, i have a code for Encrypting passwords...
    but i dont know why for some combination of words(like IS) it show "Unclosed character string " when updating the Crypted text to SQl..

    here's my code
    Code:
    Public Function IsChangePassword(ByVal user As String, ByVal oldPasswd As Object, _
                              ByVal newPasswd As Object, ByVal retype As Object) As Boolean
    On Error GoTo HELL
    
      Dim sOldPasswd$, sNewPasswd$, sRetype$
      Dim sSQl As String
      Dim rec As New ADODB.Recordset
      
      sOldPasswd = Crypt(oldPasswd)
      sNewPasswd = Crypt(newPasswd)
      sRetype = Crypt(retype)
        
      sSQl = ""
      sSQl = sSQl & " select count(*) from t_username "
      sSQl = sSQl & " where user_name = " & QuoteStr(user)
      sSQl = sSQl & " and user_passwd = " & QuoteStr(sOldPasswd)
      Set rec = conn.Execute(sSQl)
      If rec.Fields(0) = 0 Then
        IsChangePassword = False
        MsgBox "Old password does not match !", vbInformation, "Change Password"
        oldPasswd.SetFocus
        Exit Function
      End If
      
      If sNewPasswd <> sRetype Then
        IsChangePassword = False
        MsgBox "Retype new password does not match !", vbInformation, "Change Password"
        retype.SetFocus
        Exit Function
      End If
      
      sSQl = ""
      'sSQL = sSQL & " update t_username set user_passwd = " & QuoteStr(sNewPasswd) & " where user_name = " & QuoteStr(user)
      sSQl = sSQl & " update t_username set user_passwd = '" & sNewPasswd & "' where user_name = '" & user & "'"
      ExecuteSQL sSQl, False
      
      IsChangePassword = True
      MsgBox "Password successfully changed !", vbInformation, "Change Password"
      
      Set rec = Nothing
    
    HELL:
      If Err.Number <> 0 Then
        IsChangePassword = False
        Call LogActivities(Now(), "", Err.Number, Err.Source, Err.Description, "IsChangePassword() As Boolean", App.Major & "." & App.Minor & "." & App.Revision, "user As String, oldPasswd As Object, newPasswd As Object, retype As Object", vTypeError)
        MsgBox Err.Description, vbCritical, "IsChangePassword @ " & App.Title & ".cGenUser"
        Set rec = Nothing
        Exit Function
      End If
    but when i use the Sql Code for updating pass to SQl in Query analizer it works fine,,
    but why when VB catch an err. message on it.. ??
    Plz help..

  2. #2
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    Re: "Unclosed quote character mark before.." on updating crypted password to SQl

    Sql is a funny beast .... There are a few things i always check before adding a string to the SQL query...

    1) Double up on the single quote marks. - Sql uses ' to mark strings, and if you need to store one in the DB double it up... IE. for every ' in the string replace it with '' . The SQL treats this the same like VB treats "" , and places one single quote in the DB, Or prefix with a slash IE. \'

    2) Check for comment markers, there are several in SQL (#, --, /*...*/), although if used between quotes they are considered as part of the string. (however a freak combination of control chars can break it), and either uniquely code them (prefixing works best), or remove them.

    3) Avoid using newline markers (&0A , &0D) unless really needed..
    Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
    WPF Articles : 3D Animation 1 , 2 , 3
    Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
    Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
    All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.

  3. #3
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,981

    Re: "Unclosed quote character mark before.." on updating crypted password to SQl

    Look up SET QUOTED IDENTIFIER = ON in the SQL Configuration
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  4. #4
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,722

    Re: "Unclosed quote character mark before.." on updating crypted password to SQl

    I'd inspect the encryption results. You have to make sure to create alphanumeric characters only. If an encrypted password might contain special characters, like Gremlin has hinted, as there are ', /* and os on, string integrity might be destroyed.

  5. #5
    Join Date
    Jul 2012
    Posts
    6

    Re: "Unclosed quote character mark before.." on updating crypted password to SQl

    Quote Originally Posted by GremlinSA View Post
    Sql is a funny beast .... There are a few things i always check before adding a string to the SQL query...

    1) Double up on the single quote marks. - Sql uses ' to mark strings, and if you need to store one in the DB double it up... IE. for every ' in the string replace it with '' . The SQL treats this the same like VB treats "" , and places one single quote in the DB, Or prefix with a slash IE. \'

    2) Check for comment markers, there are several in SQL (#, --, /*...*/), although if used between quotes they are considered as part of the string. (however a freak combination of control chars can break it), and either uniquely code them (prefixing works best), or remove them.

    3) Avoid using newline markers (&0A , &0D) unless really needed..
    thx for reply to my thread,,
    i said, when i use Query Analizer the crypted password is succesfully update, but when i update the crypted password through VB 6 it's always show that error message..

  6. #6
    Join Date
    Jul 2012
    Posts
    6

    Re: "Unclosed quote character mark before.." on updating crypted password to SQl

    Quote Originally Posted by dglienna View Post
    Look up SET QUOTED IDENTIFIER = ON in the SQL Configuration
    i cannot understand that .. Lolz..

  7. #7
    Join Date
    Jul 2012
    Posts
    6

    Re: "Unclosed quote character mark before.." on updating crypted password to SQl

    Quote Originally Posted by WoF View Post
    I'd inspect the encryption results. You have to make sure to create alphanumeric characters only. If an encrypted password might contain special characters, like Gremlin has hinted, as there are ', /* and os on, string integrity might be destroyed.
    well, i think that's the problem..
    but i dont know how to resolve that problem...

  8. #8
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    Re: "Unclosed quote character mark before.." on updating crypted password to SQl

    Several options, Change the Encryption method to not include special chars... (use Hashing methods)
    Convert the encryption string to the ASCII Hexcodes and store those... Example:

    encryption= Qw@$l)'
    Store :517740246C2927

    These are easy to compare, and still maintain the encryption integrity...

    The main reason why it might work in the Query analyser is that it passes the query slightly differently to how VB6 would, also ARE YOU SURE you using exactly the same encrypted password that VB6 is trying to pass..

    Something else to ponder...
    Code:
    update t_username set user_passwd = '[Password]' where user_name = '[Username]'
    and
    Code:
    update t_username
    set user_passwd = '[Password]' 
    where user_name = '[Username]'
    Are very different queries...

    while they return the same results. The line breaks cause them to be processed differently..

    and if there is some problem in lets say Password, like a single quote followed by comment marker these are the resulting Query's for the above two.

    Code:
    update t_username set user_passwd = '[Pass]' -- [word]' where user_name = '[Username]'
    Code:
    update t_username
    set user_passwd = '[Pass]' -- [word]'  
    where user_name = '[Username]'
    as you can see with what i highlighted in green is now considered a SQL comment, and in the two query's, the comment parts are vastly different and change the final query...
    Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
    WPF Articles : 3D Animation 1 , 2 , 3
    Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
    Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
    All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.

Posting Permissions

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


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center