MySQLi and prepared statements

[rwhite35]

I'm new to the board and noticed a lot of post were using the mysql_* function. I thought it might be helpful to post an example query using a pre scrubber (for security) and prepared statements. This is more for my benefit, as it helps me document the whole machinery in one short script. If you have suggestions on how to improve any part of this, please comment. All feedback is welcome.

Code: Select all

// contains constants DB_HOST, DB_USER, etc
include(‘.db_credentials’);

// assign $_POST elements to scalar variables
if (!empty($_POST)){
  foreach($_POST as $key=>$value){
	${$key} = $value;
	// $user_name = $_POST['user_name']
	// $user_pwd = $_POST[‘user_pwd’]
	}
}

// scrub user input, a belt and suspenders approach
//usage:  $var = sanitize_system_string($string,minChar,maxChar)
$user_name = sanitize_system_string($user_name,2,44);  //overloading vars
$user_pwd = sanitize_system_string($user_pwd,2,44);  //overloading vars

// using Object Oriented style and built in mysqli
$mysqli = new mysqli(DB_HOST,DB_USER,DB_PWD,DB_NAME);
if ($mysqli->connect_errno){
    $err = urlencode("Failed to open database connection: ".$mysqli->connect_error);
       header("Location: error.php?err=$err");
       exit();
    }

// using prepared statements
if ($stmt = $mysqli->prepare("SELECT user_id, user_phone FROM usertable WHERE user_name=? AND user_pwd=?")){  // note, no “;” at end of statement
 // bind the var to the statement parameter
 $stmt->bind_param('ss',$user_name,$user_pwd);  // s for string, one per var
 $stmt->execute();
 // bind $stmt resultset to an object variable
 $stmt->bind_result($col1,$col2); // col1:user_id, col2:user_phone
  
 // using fetch() to get a result from the prepared statement
  while ($stmt->fetch()){
		$userID=$col1;
		$userPhone=$col2;
	}
	$stmt->close();
}
$mysqli->close();

echo $userID; //outputs user id 
echo $userPhone; //outputs phone number

# function sanitize_system_string
# sanitize a string in prep for passing a single argument to query
# no piping or passing possible environment variables ($),
# seperate commands, nested execution, file redirection, 
# background processing, special commands (backspace, etc.), quotes
# newlines, or some other special characters

function sanitize_system_string($string, $min='', $max='')
{
  $pattern = '/(;|\||`|>|<|&|^|"|'."\n|\r|'".'|{|}|[|]|\)|\()/i'; 
  $string = preg_replace($pattern, '', $string);
  
  //make sure this is only interpretted as ONE argument
  $string = preg_replace('/\$/', '\\\$', $string);
  $len = strlen($string);
  
  if((($min != '') && ($len < $min)) || (($max != '') && ($len > $max)))
  return FALSE;
  return $string;
}

This is the preferred approach (according to the PHP uberlords) and the deprecation process has started for mysql_*. So it would be best to start using prepared statements for any new code. Plus it is safer and has machinery (in bind_param process) to help prevent MySQL injection attacks!
Good Luck!

[NigelRen]

One question is - why are you 'sanitizing' strings which are then put into bind variables? This is OK if it's part of your ideas of what a user name and password should be, but not really useful as a general example. The thing I really like about bind variables is that you can safely cope with anything the user puts in without having to worry about SQL Injection problems.

One problem is that in your sanitize function returns false if the strings don't match the length criteria - and then you don't bother checking if this has happened before plugging them into the bind variables.

Also to simplify the code in the sanitize function, if you set the defaults for $min and $max to 0 and PHP_INT_MAX, then the if statement simply needs to check the length against these values and not bother with the ''.

Lastly from style point of view, it's better ( IMHO ) to write the if with braces

Code: Select all

if($len < $min || $len > $max) {
  return FALSE;
}

[rwhite35]

Nigel, Thanks for the reply. I suppose it is overkill, but I've had a compromised server before and now prefer more than one line of defense. You're right about the 0 character input, I should add some exit routine that would give some user feedback. Regards,