close ad
 
Important WebAssist Announcement
open ad
View Menu

Web development tutorial

Using a MySQLi connection with Security Assist

Tutorial created by CraigR, ForthWebSolutions

Categories: Data Bridge, MySQLi Server Behaviors, MySQL

rating

I really like Security Assist (SA).

Whilst it is pretty straightforward to create a login system with MySQLi, the additional benefits of access-level authentication via access rules, which are built into SA, make the management of site security much easier

arrow downIntroduction

I really like Security Assist (SA).
Whilst it is pretty straightforward to create a login system with MySQLi, (Tutorial)
the additional benefits of access-level authentication via access rules, which are built into SA, make the management of site security much easier.
However, 2 years after the release of MySQLi Server Behaviors, I am still having to create 2 connections to my websites, one for MySQLi and one for MySQL.
In fact the SA wizard will not work unless a MySQL connection is present.
So, I thought I would have a go at rectifying this, and look through the pages created by SA to see if I could update them, pending a new version of Data Bridge from the WA team

arrow downBackground

Here is what I did for my scenario, read on, but the next paragraph is pertinent to my own project, it is just to give you an idea of your prerequisites.
I created a new website and added a few pages which were working from an existing project, namely login, logout, forgottenpassword, forgottenencryptedpassword and resetpassword.
I also copied a single table from an existing site database, tblmembers, which contained the username, email, name, surname, password and userLevel fields I needed to look up when trying to log in
In Dreamweaver, I then went into Webassist->SecurityAssist->Manage Site Access->Access Rules Manager and I created 2 new rules, Admin and User.
For Admin, the rule was that AllowIf SESSION['UserLevel'] = 1, (so in my members table, userLevel is 1 for Admins)
For User, the rules were AllowIf SESSION['UserLevel'] = 1 AND AllowIf SESSION['UserLevel'] = 2, (so Admins also have access to user pages, when rules were set in the access pages manager)

arrow downThe Update

The upshot of this, is that some pages are created by the WA wizard, and these are stored in the Webassist->security_assist folder.
The key one here, is helper_php.php which is the one we are going to change.
Also note that whenever the WA wizard is used, this page is re-created and will overwrite any changes you make, so either give it a different name, and instead include the renamed page in your <?php require_once("webassist/security_assist/helper_php.php" );?> calls, or ensure you keep a backup of your modified page and use this.

arrow downChanging helper_php.php

Looking through the code of helper_php.php, there are a number of functions which use MySQL connections, create recordsets and manipulate the returned row values
All I did was to look through these functions and update the MySQL to MySQLi.

Eg from function WA_AuthenticateUser, I changed

function WA_AuthenticateUser($WA_Auth_Parameter){
$UserAuthenticated = false;
mysql_select_db($WA_Auth_Parameter["database"], $WA_Auth_Parameter["connection"]);
$WA_Auth_loginSQL = "SELECT `".implode('`,`', $WA_Auth_Parameter["sessionColumns"])."` FROM `".$WA_Auth_Parameter["tableName"]."` WHERE ";
for($idx=0;$idx<count($WA_Auth_Parameter["columns"]);$idx++){
$WA_Auth_loginSQL .= sprintf((($idx!=0)?" AND ":" ")."`%s`=%s ", $WA_Auth_Parameter["columns"][$idx], WA_GetSQLValueString($WA_Auth_Parameter["columnValues"][$idx], $WA_Auth_Parameter["columnTypes"][$idx]));
}
$WA_Auth_RS = mysql_query($WA_Auth_loginSQL, $WA_Auth_Parameter["connection"]) or die(mysql_error());
$WA_Auth_Rows = mysql_num_rows($WA_Auth_RS);
if($WA_Auth_Rows){
$UserAuthenticated = true;
$idx = 0;
foreach ($WA_Auth_Parameter["sessionNames"] as $sessionName){
$_SESSION[$sessionName] = mysql_result($WA_Auth_RS,0,$WA_Auth_Parameter["sessionColumns"][$idx]);
$idx++;
}



to

function WA_AuthenticateUser($WA_Auth_Parameter){
$UserAuthenticated = false;
$WA_Auth_RS = new WA_MySQLi_RS($WA_Auth_Parameter["database"],$WA_Auth_Parameter["connection"],0);
$WA_Auth_loginSQL = "SELECT `".implode('`,`', $WA_Auth_Parameter["sessionColumns"])."` FROM `".$WA_Auth_Parameter["tableName"]."` WHERE ";
for($idx=0;$idx<count($WA_Auth_Parameter["columns"]);$idx++){
$WA_Auth_loginSQL .= sprintf((($idx!=0)?" AND ":" ")."`%s`=%s ", $WA_Auth_Parameter["columns"][$idx], WA_GetSQLValueString($WA_Auth_Parameter["columnValues"][$idx], $WA_Auth_Parameter["columnTypes"][$idx]));
}
//echo $WA_Auth_loginSQL;
$WA_Auth_RS->setQuery($WA_Auth_loginSQL);
$WA_Auth_RS->execute();
//echo $WA_Auth_RS->TotalRows;

if($WA_Auth_RS->TotalRows > 0){
$UserAuthenticated = true;
foreach($WA_Auth_RS->Results[0] as $key => $val) {
$_SESSION[$key] = $val;
//echo 'Session variable ' . $key . ' is: ' . $val . '<br />';
}
...



i have left some of the comments intact, as there was a bit of trial and error involved, but after a few hours, I now have all of my login/logout pages etc working as before.

arrow downIn a nutshell

Simply adding the updated helper_php.php to your site, (don't forget to backup the original) and adding the appropriate connections and mysqli required pages to the pages which use it, and you should be good to go

arrow downUpdated File

Note that on the pages which previously required helper_php.php, instead of your mysql connection, you now require your MySqli connection and 'webassist/mysqli/rsobj.php', (and possibly 'webassist/mysqli/queryobj.php' for db updates).

Attached is the updated helper_php.php.

helper_php.php


I am sure there are improvements to be made, but it is at least a start, and any comments or suggestions could be used to take it further

arrow downReviews and comments

Comments will be sent to the author of this tutorial and may not be answered immediately. For general help from WebAssist, please visit technical support.

Sign in to add comments
rating

: 7 Years, 8 Months, 4 Weeks, 1 Day, 4 Hours, 18 Minutes ago

hai sir really i cannot understand

CraigR: 7 Years, 8 Months, 4 Weeks, 5 Hours, 41 Minutes ago

can you explain what you don't understand ?

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.