We help IT Professionals succeed at work.

Using sp_password in PB

quark122
quark122 asked
on
Medium Priority
1,303 Views
Last Modified: 2013-12-26
Hi,

Currently, I change a user's password as follows:

ls_SQLText = 'sp_password ' + sapass + ', ' + newpass + ', ' + is_user
EXECUTE IMMEDIATE :ls_SQLText;


That works fine EXCEPT...

if sapass or newpass contain special characters (pretty much anything but an alpha or numeric).

How can I change the command to handle special characters?

--Will
Comment
Watch Question

Hi,

Have you tried using other methods of calling a procedure from PB like declaring it s RPCFUNC on transaction object or the DECLARE/EXECUTE syntax ?

Regards,
VIkas
CERTIFIED EXPERT

Commented:
Is it sybase or sql server?

Well. It will work for all except single quote (') itself.

ls_SQLText = 'sp_password ' + sapass + ', ' + newpass + ', ' + is_user
EXECUTE IMMEDIATE :ls_SQLText;

If sapass or newpass consist of single quote then replace single quote with two single quotes ('') then it will work.

Write something like:

ll_pos = pos(sapass, "'")
If  ll_pos > 0 then
   sapass = replace(sapass, ll_pos , 1, "''")  // double quote single quote single quote double quote
end if

You may need to a loop to replace single quotes.

You can also a create a datawindow outof sp_password (d_pass) system proc and then code like this.

datastore lds_sp
lds_sp = create datastore

lds_sp.dataobject = 'd_pass'
lds_sp.SetTransObject(SQLCA)
lds_sp.Retrieve(sapass , newpass, is_user)


regards-

Author

Commented:
It's sybase 12.0.3/4/6 depending on the installation site.

From what I've encountered with customers attempting to use this code, it doesn't work with any special character.  sp_password errors with syntax issues at the special character.  (!, @, #, etc).

--Will
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Sybase does allow special characters in passwords. I've got users with passwords right now (set through sybase central I presume) that have special characters.  

I knew I could mask off special characters, but since the actual corporate protocol requires upper, lower, numeric & special character for passwords, I was trying to make this system comply as well...  Oh well.  

Thanks Namasi, points awarded.

--Will
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.