"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.. :D
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..:confused::confused:
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..
Re: "Unclosed quote character mark before.." on updating crypted password to SQl
Look up SET QUOTED IDENTIFIER = ON in the SQL Configuration
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.
Re: "Unclosed quote character mark before.." on updating crypted password to SQl
Quote:
Originally Posted by
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..
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..
:(:confused:
Re: "Unclosed quote character mark before.." on updating crypted password to SQl
Quote:
Originally Posted by
dglienna
Look up SET QUOTED IDENTIFIER = ON in the SQL Configuration
i cannot understand that .. Lolz.. :wave:
Re: "Unclosed quote character mark before.." on updating crypted password to SQl
Quote:
Originally Posted by
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.
well, i think that's the problem..
but i dont know how to resolve that problem...
:wave::confused:
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...