note 103373 added to mysqli.prepare
| From: | KrisCraig@php.net | Date: | Mon, 11 Apr 2011 08:33:28 +0000 |
| Subject: | note 103373 added to mysqli.prepare | ||
| Groups: | php.notes | ||
| Request: | Send a blank email to php-notes+get-178675@lists.php.net to get a copy of this message | ||
I've been "schooling" myself these last few days on switching to prepared statements
using mysqli after an acquaintance reported being the victim of an SQL injection attack recently.
After borrowing a couple functions from past contributors on here and some trial and error of my
own, I've come up with a streamlined class that greatly simplifies the whole process.
Here's what I came up with:
<?php
class sql
{
function init()
{
require( "your-mysqli-connect-vars.php" );
$db = new mysqli( $sql_host, $sql_user, $sql_pass, $sql_db );
return $db;
}
function query( $db, $query, $params = array(), $returntype = 1 )
{
$stmt = $db->prepare( $query ) or die( "Error preparing SQL statement" );
$args = array();
$args[0] = NULL;
foreach ( $params as $var => $val )
{
if ( is_numeric( $val ) )
{
$vartype = "i";
}
else
{
$vartype = "s";
}
$$var = $val;
$args[0] .= $vartype;
$args[] = $$var;
}
if ( !empty( $params ) )
{
call_user_func_array( array( $stmt, "bind_param" ), self::sanitize( $args ) );
}
$stmt->execute();
switch ( $returntype )
{
default:
case 0:
$returnval = NULL;
break;
case 1:
$returnval = self::fetch( $stmt );
break;
case 2:
$returnval = $stmt->affected_rows;
break;
case 3:
$stmt->store_result();
$returnval = $stmt->num_rows();
break;
case 4:
$returnval = $stmt;
break;
}
$stmt->close();
return $returnval;
}
/* Original function by fabio at kidopi dot com dot br, posted at http://www.php.net/manual/en/mysqli-stmt.bind-param.php.
--Kris */
function sanitize( $arr )
{
if ( strnatcmp( phpversion(), "5.3" ) >= 0 )
{
$arr2 = array();
foreach ( $arr as $arrkey => $arrval )
{
$arr2[$key] =& $arr[$key];
}
return $arr2;
}
else
{
return $arr;
}
}
/* Original function by nieprzeklinaj at gmail dot com, posted at http://www.php.net/manual/en/mysqli-stmt.bind-result.php.
--Kris */
function fetch( $result )
{
$array = array();
if ( $result instanceof mysqli_stmt )
{
$result->store_result();
$variables = array();
$data = array();
$meta = $result->result_metadata();
while ( $field = $meta->fetch_field() )
{
$variables[] = &$data[$field->name]; // pass by reference
}
call_user_func_array( array( $result, "bind_result" ), $variables );
$i = 0;
while( $result->fetch() )
{
$array[$i] = array();
foreach ( $data as $k => $v )
{
$array[$i][$k] = $v;
}
$i++;
// don't know why, but when I tried $array[] = $data, I got the same one result in all rows
}
}
else if( $result instanceof mysqli_result )
{
while ( $row = $result->fetch_assoc() )
{
$array[] = $row;
}
}
return $array;
}
function close( $db )
{
return $db->close();
}
}
?>
----
Server IP: 69.147.83.197
Probable Submitter: 174.21.233.183
----
Manual Page -- http://www.php.net/manual/en/mysqli.prepare.php
Edit -- https://master.php.net/note/edit/103373
Del: integrated -- https://master.php.net/note/delete/103373/integrated
Del: useless -- https://master.php.net/note/delete/103373/useless
Del: bad code -- https://master.php.net/note/delete/103373/bad+code
Del: spam -- https://master.php.net/note/delete/103373/spam
Del: non-english -- https://master.php.net/note/delete/103373/non-english
Del: in docs -- https://master.php.net/note/delete/103373/in+docs
Del: other reasons-- https://master.php.net/note/delete/103373
Reject -- https://master.php.net/note/reject/103373
Search -- https://master.php.net/manage/user-notes.php