Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

MySQLi and prepared statements

Codes here !

Moderators: macek, egami, gesf

MySQLi and prepared statements

Postby rwhite35 » Thu Sep 27, 2012 4:33 pm

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!
rwhite35
New php-forum User
New php-forum User
 
Posts: 13
Joined: Thu Sep 27, 2012 10:55 am

Re: MySQLi and prepared statements

Postby NigelRen » Fri Sep 28, 2012 4:06 am

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;
}
NigelRen
php-forum Active User
php-forum Active User
 
Posts: 450
Joined: Fri Aug 05, 2011 9:53 am

Re: MySQLi and prepared statements

Postby rwhite35 » Sun Sep 30, 2012 4:45 pm

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,
rwhite35
New php-forum User
New php-forum User
 
Posts: 13
Joined: Thu Sep 27, 2012 10:55 am


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 4 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.