Using a MySQLi connection with Security Assist

Tutorial created by CraigR, ForthWebSolutions

Categories: Data Bridge, MySQLi Server Behaviors, MySQL

1 ratings | Sign in to rate this tutorial | View comments

next section

Introduction

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

next section

Background

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)

next section

The 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.

next section

Changing 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.

next section

In 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

Updated 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

Reviews 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.

1 ratings | Sign in to rate this tutorial