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

[ryanframes]

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

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..

[GremlinSA]

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..

[dglienna]

Look up SET QUOTED IDENTIFIER = ON in the SQL Configuration

[WoF]

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.

[ryanframes]

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..

[ryanframes]

Look up SET QUOTED IDENTIFIER = ON in the SQL Configuration

i cannot understand that .. Lolz..

[ryanframes]

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...

[GremlinSA]

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...

update t_username set user_passwd = '[Password]' where user_name = '[Username]'

and

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.

update t_username set user_passwd = '[Pass]' -- [word]' where user_name = '[Username]'

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...