Want to streamline Prepared Statement

[doubledee]

To keep my website more secure, I am currently using Prepared Statement.

 

The problem is that even the simplest query (e.g. retrieve Member's First Name) involves an INSANE amount of code.

 

Here is an example of what my code looks like...

// **********************
// Find Member Salt.		*
// **********************

// Build query.
$q2 = 'SELECT salt
		FROM member
		WHERE email=?';

// Prepare statement.
$stmt2 = mysqli_prepare($dbc, $q2);

// Bind variables to query.
mysqli_stmt_bind_param($stmt2, 's', $email);

// Execute query.
mysqli_stmt_execute($stmt2);

// Store results.
mysqli_stmt_store_result($stmt2);

// Check # of Records Returned.
if (mysqli_stmt_num_rows($stmt2)==1){
	// Member found.
	// Bind result-set to variables.
	mysqli_stmt_bind_result($stmt2, $salt);

	// Fetch record.
	mysqli_stmt_fetch($stmt2);

	// Close prepared statement.
	mysqli_stmt_close($stmt2);
}else{
	$salt='';
//		$errors['pass'] = 'A System Error has occurred.';
}// End of FIND MEMBER SALT

 

I am wondering if there is a way to streamline things, perhaps by creating a Function where I can just pass arguments to my specific query needs.  Of course if I do that, then a bad guy could pass arguments to the same Function and possibly doing something really bad?!

 

Any ideas?

 

Thanks,

 

 

Debbie

 

P.S.  I am not ready to jump in to OOP, so please keep any responses using old-fashioned Procedural Programming.  Thanks!

 

 

 

[darkfreaks]

just a bit of advice you can streamline your if's with ternary operators

 

 

$check_rows= ($stmt2=>1) ? 'code if true' : 'code if false'; //streamlined ternary operator code for rows

 

also i think this MYSQL functions can be altered to fit MYSQLI

 

http://www.techrepublic.com/article/six-mysqlphp-functions-to-streamline-development/1045433

 

 

[kicken]

The mysqli api doesn't really lend itself well to wrapping things up in functions.  After thinking about it for a bit this seems to work, but I did very limited testing and there may be problems with more advanced queries or usages.

 

<?php

function mysqli_streamlined_query($link, $sql, $params, &$row){
$stmt = mysqli_prepare($link, $sql);
if (!$stmt){
	throw new Exception('Unable to prepare query '.$sql);
}

if (($pcnt = count($params)) > 0){
	$columnSpec = str_repeat('s', $pcnt);

	$bindParams=array($stmt, $columnSpec);
	foreach ($params as &$p){
		$bindParams[] =& $p;
	}

	$ret = call_user_func_array('mysqli_stmt_bind_param', $bindParams);
	if (!$ret){
		throw new Exception('Unable to bind query parameters: '.print_r($params, true));
	}
}

if (!mysqli_stmt_execute($stmt)){
	throw new Exception('Unable to execute query');
}

$meta = mysqli_stmt_result_metadata($stmt);
if (!$meta){
	throw new Exception('Unable to get result metadata');
}

$fields = mysqli_fetch_fields($meta);
if (!$fields){
	throw new Exception('Unable to get result field list.');
}

$bindResults = array($stmt);
foreach ($fields as $f){
	$row[$f->name] = null;
	$bindResults[] =& $row[$f->name];
}

$ret = call_user_func_array('mysqli_stmt_bind_result', $bindResults);
if (!$ret){
	throw new Exception('Unable to bind result variables.');
}

return $stmt;
}

 

Example usage:

<?php

require_once 'mysqli_streamlined_query.inc.php';

$dbc = mysqli_connect('127.0.0.1', 'user', 'pass', 'db');

$sql = '
SELECT
uid,
email,
firstName,
lastName,
lastLogin
FROM table_users
WHERE
uid=?
';

$params = array(67);
$row = array();
$stmt = mysqli_streamlined_query($dbc, $sql, $params, $row);

while (mysqli_stmt_fetch($stmt)){
     var_dump($row);
}

 

 

[doubledee]

The mysqli api doesn't really lend itself well to wrapping things up in functions.  After thinking about it for a bit this seems to work, but I did very limited testing and there may be problems with more advanced queries or usages.

 

<?php

function mysqli_streamlined_query($link, $sql, $params, &$row){
$stmt = mysqli_prepare($link, $sql);
if (!$stmt){
	throw new Exception('Unable to prepare query '.$sql);
}

if (($pcnt = count($params)) > 0){
	$columnSpec = str_repeat('s', $pcnt);

	$bindParams=array($stmt, $columnSpec);
	foreach ($params as &$p){
		$bindParams[] =& $p;
	}

	$ret = call_user_func_array('mysqli_stmt_bind_param', $bindParams);
	if (!$ret){
		throw new Exception('Unable to bind query parameters: '.print_r($params, true));
	}
}

if (!mysqli_stmt_execute($stmt)){
	throw new Exception('Unable to execute query');
}

$meta = mysqli_stmt_result_metadata($stmt);
if (!$meta){
	throw new Exception('Unable to get result metadata');
}

$fields = mysqli_fetch_fields($meta);
if (!$fields){
	throw new Exception('Unable to get result field list.');
}

$bindResults = array($stmt);
foreach ($fields as $f){
	$row[$f->name] = null;
	$bindResults[] =& $row[$f->name];
}

$ret = call_user_func_array('mysqli_stmt_bind_result', $bindResults);
if (!$ret){
	throw new Exception('Unable to bind result variables.');
}

return $stmt;
}

 

Example usage:

<?php

require_once 'mysqli_streamlined_query.inc.php';

$dbc = mysqli_connect('127.0.0.1', 'user', 'pass', 'db');

$sql = '
SELECT
uid,
email,
firstName,
lastName,
lastLogin
FROM table_users
WHERE
uid=?
';

$params = array(67);
$row = array();
$stmt = mysqli_streamlined_query($dbc, $sql, $params, $row);

while (mysqli_stmt_fetch($stmt)){
     var_dump($row);
}

 

Is there a security risk that someone could alter the Prepared Statement call and do nefarious things?

 

And backing up...

 

How do you all organize your code to make it more readable and manageable when you have multiple calls to your database in one script?

 

For example, in my Log-In script, I need to do the following...

 

- Check if the Member is Activated

- Check if there is a Salt

- Find Member to log them in

 

(My Change Password script is even more convoluted...)

 

 

Debbie

 

[darkfreaks]

i think this would work to at least work for what you want with salt

 

http://stackoverflow.com/questions/4282199/please-quickly-check-this-php-salt-implementation-does-not-work