Solved

Using sp_password in PB

Posted on 2004-04-08
5
1,197 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
0
Comment
Question by:quark122
  • 2
  • 2
5 Comments
 
LVL 8

Expert Comment

by:Vikas_Dixit
Comment Utility
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
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
Comment Utility
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-
0
 

Author Comment

by:quark122
Comment Utility
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
0
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 125 total points
Comment Utility
Will,

Sql server allows sepacila characters in password. Sybase does not. Have you tried to create a password with special character from isql? I am sure that will fail too.

You can force the user not to enter special characters by making sle to editmask. Or you can still use sle but pop up an error message if user enters a special character. You can use match function for that. If password is 8 charchers then

If Match(newpass, "[A-Za-z0-9][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9]") = false then
  MessageBox("Error", "Password must consist of alphanumeric characters only.")
end if

See helpfile for other pattern matching options.

regards-

Straight from help file.

Description

Determines whether a string's value contains a particular pattern of characters.

Syntax

Match ( string, textpattern )

Argument      Description
string      The string in which you want to look for a pattern of characters
textpattern      A string whose value is the text pattern
Return value

Boolean. Returns TRUE if string matches textpattern and FALSE if it does not. Match also returns FALSE if either argument has not been assigned a value or the pattern is invalid. If any argument's value is NULL, Match returns NULL.

Usage

Match enables you to evaluate whether a string contains a general pattern of characters. To find out whether a string contains a specific substring, use the Pos function.
Textpattern is similar to a regular expression. It consists of metacharacters, which have special meaning, and ordinary characters, which match themselves. You can specify that the string begin or end with one or more characters from a set, or that it contain any characters except those in a set.

A text pattern consists of metacharacters, which have special meaning in the match string, and nonmetacharacters, which match the characters themselves.
The following tables explain the meaning and use of these metacharacters:

Metacharacter      Meaning      Example
Caret (^)      Matches the beginning of a string      ^C matches C at the beginning of a string
Dollar sign ($)      Matches the end of a string      s$ matches s at the end of a string
Period (.)      Matches any character      . . . matches three consecutive characters
Backslash (\)      Removes the following metacharacter's special characteristics so that it matches itself      \$ matches $
Character class (a group of characters enclosed in square brackets ([ ]))      Matches any of the enclosed characters      [AEIOU] matches A, E, I, O, or UYou can use hyphens to abbreviate ranges of characters in a character class. For example, [A-Za-z] matches any letter
Complemented character class (first character inside the brackets is a caret)      Matches any character not in the group following the caret      [^0-9] matches any character except a digit, and [^A-Za-z] matches any character except a letter
The metacharacters asterisk (*), plus (+), and question mark (?) are unary operators that are used to specify repetitions in a regular expression:

Metacharacter      Meaning      Example
* (asterisk)      Indicates zero or more occurrences      A* matches zero or more As (no As, A, AA, AAA, and so on)
+ (plus)      Indicates one or more occurrences      A+ matches one A or more than one A (A, AAA, and so on)
? (question mark)      Indicates zero or one occurrence      A? matches an empty string ("") or A
Sample patterns  
The following table shows various text patterns and sample text that matches each pattern:

This pattern      Matches
AB      Any string that contains AB; for example, ABA, DEABC, graphAB_one
B*      Any string that contains 0 or more Bs; for example, AC, B, BB, BBB, ABBBC, and so on
AB*C      Any string containing the pattern AC or ABC or ABBC, and so on (0 or more Bs)
AB+C      Any string containing the pattern ABC or ABBC or ABBBC, and so on (1 or more Bs)
ABB*C      Any string containing the pattern ABC or ABBC or ABBBC, and so on (1 B plus 0 or more Bs)
^AB      Any string starting with AB
AB?C      Any string containing the pattern AC or ABC (0 or 1 B)
^[ABC]      Any string starting with A, B, or C
[^ABC]      A string containing any characters other than A, B, or C
^[^abc]      A string that begins with any character except a, b, or c
^[^a-z]$      Any single-character string that is not a lowercase letter (^ and $ indicate the beginning and end of the string)
[A-Z]+      Any string with one or more uppercase letters
^[0-9]+$      Any string consisting only of digits
^[0-9][0-9][0-9]$      Any string consisting of exactly three digits
^([0-9][0-9][0-9])$      Any consisting of exactly three digits enclosed in parentheses

regards-

0
 

Author Comment

by:quark122
Comment Utility
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
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deletiā€¦
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now