Updating form fields a different table

Applications Forms
#1

I am developing a project in which a form pulls data from a table named ResourceRequests. It includes fields for the user’s telephone numbers, which are already stored in the sec_users table. When the form is loaded, I would like the initial value of the contact information fields in this form to be set from the values in the sec_users table.

I added some lines of code in the Events > onValidate section of the sec_Login application to create global variables from the desired fields in the sec_users table. (These lines are marked below with the comment “# ADDED LINE”.) However, after adding this code, when I ran the login application and entered my login credentials, the application generated the error message “Sorry, but user is not active!” I then deleted the added lines, and I was able to login successfully. I find this a complete mystery.

How can I set these variables?


$slogin = sc_sql_injection({login});
$spswd = sc_sql_injection(({pswd}));

$sql = "SELECT
priv_admin,
active,
name,
email,
uOfficePhone, # ADDED LINE
uOfficeExt, # ADDED LINE
uOfficeMobilePhone # ADDED LINE
FROM sec_users
WHERE login = $slogin
AND pswd = ".$spswd."";

sc_lookup(rs, $sql);

if(count({rs}) == 0)
{
sc_log_add('login Fail', {lang_login_fail} . {login});
sc_error_message({lang_error_login});
sc_error_exit();
}
else if({rs[0][1]} == 'Y')
{
$usr_login = {login};
$usr_priv_admin = ({rs[0][0]} == 'Y') ? TRUE : FALSE;
$usr_name = {rs[0][2]};
$usr_email = {rs[0][3]};
$usr_off_phone = {rs[0][4]};  # ADDED LINE
$usr_off_ext = {rs[0][5]};  # ADDED LINE
$$user_mobile_phone = {rs[0][6]};  # ADDED LINE

sc_set_global($usr_login);
sc_set_global($usr_priv_admin);
sc_set_global($usr_name);
sc_set_global($usr_email);
sc_set_global($usr_off_phone);  # ADDED LINE
sc_set_global($usr_off_ext);  # ADDED LINE
sc_set_global($usr_mobile_phone);  # ADDED LINE
}
else
{
sc_error_message({lang_error_not_active});
sc_error_exit();
}
#2

Did you add #added line for demonstration here or did you actually added this into your code? It’s between your sql statement and afaik it will not be removed from there. You have a syntax error in your sql. What you might try is to set all debugging options in application menuentry to on. Running the app will show you the generated sql to look at.

#3

I added the comments just for demonstration purposes in the quoted code. The comments do not exist in the code in the application.

I am surprised that there is a syntax error in the sql, since I based my sec_Login application on the sec_Login application that is included in the security module provided by ScriptCase. I compared my code line-by-line with the code in the original login application from ScriptCase, and I believe that I introduced no other changes than the lines that I added, which I based on existing lines in the original application. My login application appeared to work as intended before I added the lines that I marked with comments, and it worked after I once again removed them.

Are you suggesting that there is a syntax error in the sql included in the original sec_Login application from ScriptCase? Or is it in the lines that I added? I will set the debugging options in the morning and see what I can find, but can you tell me what the sql syntax error is?

I appreciate your help!

#4

Hi Eric,

as I see, you are posting two problems. For sure as I see, you got your second problem caused by the way to solve the first one. (my opinion :wink: ) So, let me describe how I would solve what you tried with the globals you set in the login-form.

Why do you want to do that? May be I did not caught the reason, but you can also put a short SELECT statement in the starting event of the form, in which you like to set these information as initial values. In my eyes, this would make more sense … Just put a small SELECT statement in Form Settings-> Events -> OnLoad and write the values directly in the fields.

I am sure, this would solve your starting problem. The second problem, I do not see a solution at the moment. Would be intering to know what the problem is. My first idea, the sequence of the vars in your select statement is not the mistake. Did you tried that behaviour (not active) with different user?

At the end, I want to give one useful hint. Do not touch this security system with any code. Even these globals you set, it would be better to set somewhere else. In case you like or you have to setup the securoty module again, I am expecting this code to be gone.

Best regards

Eric

#5

Maybe the comments inside the variable $sql is the problem

Example from Security_sc Control App, Loging - Event - Validation

[QUOTE=EricDahl;12971]I am developing a project in which a form pulls data from a table named ResourceRequests. It includes fields for the user’s telephone numbers, which are already stored in the sec_users table. When the form is loaded, I would like the initial value of the contact information fields in this form to be set from the values in the sec_users table.

I added some lines of code in the Events > onValidate section of the sec_Login application to create global variables from the desired fields in the sec_users table. (These lines are marked below with the comment “# ADDED LINE”.) However, after adding this code, when I ran the login application and entered my login credentials, the application generated the error message “Sorry, but user is not active!” I then deleted the added lines, and I was able to login successfully. I find this a complete mystery.

How can I set these variables?


$slogin = sc_sql_injection({login}); 
$spswd = sc_sql_injection(({pswd}));

$sql = "SELECT
priv_admin,
active,
name,
email,
uOfficePhone, # ADDED LINE **** Do not write comments in the sql statement
uOfficeExt, # ADDED LINE
uOfficeMobilePhone # ADDED LINE
FROM sec_users
WHERE login = $slogin
AND pswd = ".$spswd."";

sc_lookup(rs, $sql);

if(count({rs}) == 0)
{
sc_log_add('login Fail', {lang_login_fail} . {login});
sc_error_message({lang_error_login});
sc_error_exit();
}
else if({rs[0][1]} == 'Y')
{
$usr_login = {login}; //SC example code line
$usr_priv_admin = ({rs[0][0]} == 'Y') ? TRUE : FALSE; //SC example code line
$usr_name = {rs[0][2]}; //SC example code line
$usr_email = {rs[0][3]}; //SC example code line
$usr_off_phone = {rs[0][4]};  # ADDED LINE  // Your new code line
$usr_off_ext = {rs[0][5]};  # ADDED LINE // Your new code line
$$user_mobile_phone = {rs[0][6]};  # ADDED LINE // Your new code line

sc_set_global($usr_login);
sc_set_global($usr_priv_admin);
sc_set_global($usr_name);
sc_set_global($usr_email);
sc_set_global($usr_off_phone);  # ADDED LINE
sc_set_global($usr_off_ext);  # ADDED LINE
sc_set_global($usr_mobile_phone);  # ADDED LINE
}
else
{
sc_error_message({lang_error_not_active}); //"Sorry, but user is not active!"
sc_error_exit();
}

[/QUOTE]

#6

The comments in the code in the message are only in the quoted code – not the original.

I am going to try EricB’s suggestion to put the code in onLoad for the current application.

#7

Hi Eric,

I am following your suggestion by putting the following code in the onLoad event in my “app_form_ResourceRequest” application:

/**

  • Pull data from the sec_users table
    */
    $sql = “SELECT
    uOfficePhone,
    uUfficeExt,
    uMobilePhone
    FROM sec_users
    WHERE login = $usr_login”;

sc_lookup(usr_data, $sql);

$usr_office_phone        = {usr_data[0][0]};
$usr_extension        = {usr_data[0][1]};
$usr_mobile_phone    = {usr_data[0][2]};
    
sc_set_global($usr_office_phone);
sc_set_global($usr_extension);
sc_set_global($mobile_phone);

The variable in the above SELECT WHERE clause is supposed to be defined as a global variable in the app_Login application. See lines 10, 23, and 27 in the following onValidate event from app_Login:

$slogin = sc_sql_injection({login});
$spswd = sc_sql_injection(({pswd}));

$sql = “SELECT
priv_admin,
active,
name,
email
FROM sec_users
WHERE login = $slogin
AND pswd = “.$spswd.””;

sc_lookup(rs, $sql);

if(count({rs}) == 0)
{
sc_log_add(‘login Fail’, {lang_login_fail} . {login});
sc_error_message({lang_error_login});
sc_error_exit();
}
else if({rs[0][1]} == ‘Y’)
{
$usr_login = {login};
$usr_priv_admin = ({rs[0][0]} == ‘Y’) ? TRUE : FALSE;
$usr_name = {rs[0][2]};
$usr_email = {rs[0][3]};
sc_set_global($usr_login);
sc_set_global($usr_priv_admin);
sc_set_global($usr_name);
sc_set_global($usr_email);
}
else
{
sc_error_message({lang_error_not_active});
sc_error_exit();
}

However, when I login and then run app_form_ResourceRequest, the error message “Undefined variable: usr_login” is displayed and the appropriate values SELECTed from the sec_users table are not displayed in the form.

Why is the SELECT WHEN clause in my app_form_ResourceRequest application not recognizing the usr_login variable? Shouldn’t it be available for use, since it is defined as a global variable by app_Login?

What am I missing?

Eric

#8

Eric,
First of all I advise you to code like:

“SELECT uOfficePhone,uUfficeExt,uMobilePhoneFROM sec_users WHERE login = '”.$usr_login."’";

Using global variables it’s necessary to know where they are created. I actually don’t like the idea of creating them when needed as you might be declaring globals everywhere. I advise you to declare all globals once on a central spot, mostly your main application.
I use the [] syntax, but that’s personal.

$usr_login is a php variable which you have declared global. But where is it declared in your code? In the onload I would do something like:

[glob_slogin] = sc_sql_injection({login});
$spswd = sc_sql_injection(({pswd}));

$sql = “SELECT priv_admin, active, name, email, uOfficePhone, uOfficeExt, uOfficeMobilePhone FROM sec_users
WHERE login = '”.[glob_slogin]."’ AND pswd = “.$spswd.”";

#9

Hi Eric,

may be you misunderstood me in one part. If you follow my suggestion to get the values you are using in that for in the onLoad you do not need the globals anymore …

sc_lookup(usr_data, $sql);

{usr_office_phone} = {usr_data[0][0]};
{usr_extension} = {usr_data[0][1]};
{usr_mobile_phone} = {usr_data[0][2]};

expecting your field names in the form are usr_office_phone, usr_extension and usr_mobile_phone.

I hope the following Select will work:

$sql = “SELECT
uOfficePhone,
uUfficeExt,
uMobilePhone
FROM sec_users
WHERE login = '”.[usr_login]."’";

BR

Eric