note 99804 added to mysqli-stmt.bind-param
| From: | MarkReddin at osu1 dot php dot net | Date: | Wed, 08 Sep 2010 10:30:16 +0000 |
| Subject: | note 99804 added to mysqli-stmt.bind-param | ||
| Groups: | php.notes | ||
| Request: | Send a blank email to php-notes+get-172372@lists.php.net to get a copy of this message | ||
Miguel Hatrick's Statement_Parameter class, as posted in these notes, allows for a relatively
painless way of writing secure dynamic SQL. It is secure against SQL injection because we still use
bind parameters for any content coming from the user.
For example, the following code constructs an insert statement, but looks at which query string
(GET) parameters are present in order to figure out which columns should be included. The
ParameterManager.php file is simply Miguel's classes as posted in this discussion.
<?php
require_once("dbConnectionParams.php");
require_once("ParameterManager.php");
$sp = new Statement_Parameter();
$column_list = "";
$value_list = "";
if (isset ($_GET['name']) ) {
$column_list = $column_list . "name,";
$value_list = $value_list . "?,";
$sp->Add_Parameter('name', Statement_Parameter_Type::$STATEMENT_TYPE_STRING);
$sp->Set_Parameter('name',$_GET['name']);
}
if (isset($_GET['address']) ) {
$column_list = $column_list . "address,";
$value_list = $value_list . "?,";
$sp->Add_Parameter('address', Statement_Parameter_Type::$STATEMENT_TYPE_STRING);
$sp->Set_Parameter('address',$_GET['address']);
}
//tidy up column list and value list - the code above will always leave them ending in a comma,
which we remove now
$column_list = substr($column_list, 0, strlen($column_list) -1);
$value_list = substr($value_list, 0, strlen($value_list) -1);
$sql = "insert into test_table (" . $column_list . ") values (" . $value_list .
");";
echo $sql;
$mysqli = @new mysqli($host,$user,$password,$database);
$stmt = $mysqli->prepare($sql);
$sp->Bind_Params($stmt);
if($stmt->execute() === TRUE)
{
/*** assign the last insert id ***/
$last_id = $mysqli->insert_id;
echo "OK$last_id";
}
else {
echo $mysqli->error;
}
?>
----
Server IP: 69.147.83.197
Probable Submitter: 86.151.96.53
----
Manual Page -- http://www.php.net/manual/en/mysqli-stmt.bind-param.php
Edit -- https://master.php.net/note/edit/99804
Del: integrated -- https://master.php.net/note/delete/99804/integrated
Del: useless -- https://master.php.net/note/delete/99804/useless
Del: bad code -- https://master.php.net/note/delete/99804/bad+code
Del: spam -- https://master.php.net/note/delete/99804/spam
Del: non-english -- https://master.php.net/note/delete/99804/non-english
Del: in docs -- https://master.php.net/note/delete/99804/in+docs
Del: other reasons-- https://master.php.net/note/delete/99804
Reject -- https://master.php.net/note/reject/99804
Search -- https://master.php.net/manage/user-notes.php