This revolves around the PHP mysqli module (for PHP 5). Yeah, I know, it’s in the manual, but the examples aren’t straightforward enough, so here’s a couple of my own.
The idea here is SECURE database interaction. Follow these steps:
1. Create a database user with ONLY the permissions that you need for a given function. E.g. if you have a page that only needs to DISPLAY information, use a database user that only has SELECT privileges.
2. Create your database columns so they can ONLY store the types of information they need. E.g. if the field is for a phone number area code, make the column an INTEGER, not a CHAR, VARCHAR or TEXT field. Nothing bad can end up in an integer column… but if you have a wide open data-type, you could be asking for trouble.
3. Put your database connect functions ABOVE the html root directory. E.g. most web sites should utilize a directory structure with something like the following:
/html (contains all your html pages… this is the document root of the web site).
/lib (contains your database connect function and other PHP code).
4. Use regular expressions to sanitize any user input to a form. Javascript enhances the user experience, but it cannot stop someone from posting data directly to your submission page. E.g. you might have something like this to get only alpha-numeric input:
function get_alphanumeric_regex ($input) {
$pattern = '/\W/';
$input = preg_replace($pattern, ' ', $input);
return $input;
}
5. Finally, use prepared statements… they are MUCH safer than piecing together statements as strings. If someone hijacks your string, then they’ve hijacked your statement. A prepared statement can’t be messed with. With mysqli, they look something like this:
< ?php
/*-------------------------------------------------------------------------*/
define("DATABASE_HOST", 'localhost');
define("DATABASE", 'my_database');
function connect_db ($handle) {
switch($handle) {
case 'form_insert':
$link = new mysqli(DATABASE_HOST, $handle, "some_p@ssword", DATABASE);
break;
case 'form_update':
$link = new mysqli(DATABASE_HOST, $handle, "some_oth3r_p@ssword", DATABASE);
break;
case 'form_select':
$link = new mysqli(DATABASE_HOST, $handle, "ke3p_gues$ing!", DATABASE);
break;
case 'form_delete':
$link = new mysqli(DATABASE_HOST, $handle, "still-KE3p-Gu3ssing", DATABASE);
break;
}
return $link;
}
/*-------------------------------------------------------------------------*/
function insert_something ($input) {
/*
INPUT:
Hash with values from form, e.g. $input['first_name'].
OUTPUT:
id from database if successful insert; otherwise null.
*/
$link = connect_db('form_insert');
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$sql = "INSERT INTO some_table
(
first_name,
last_name,
datestamp
) VALUES (
?,
?,
NOW()
)";
$statement = $link->prepare($sql);
if (!$statement) {
printf("Error - SQLSTATE %s.\n", mysqli_sqlstate($db_connection));
exit();
};
$first_name = get_name_regex($input['first_name']);
$last_name = get_name_regex($input['last_name']);
$statement->bind_param("ss", $first_name, $last_name);
$statement->execute();
$link->close();
return $result;
}
/*-------------------------------------------------------------------------*/
function get_name_regex ($input) {
$pattern = '/(;|\||`|=|--|\/|\.|>|< |&|^|"|'."\n|\r".'|{|}|[|]|\)|\(|[0-9])/i';
$input = preg_replace($pattern, ' ', $input);
return trim(ucfirst($input));
}
?>
0 Responses to “Preparing MySQL statements in PHP 5”
Please Wait
Leave a Reply
You must login to post a comment.