Sanitising user input is a golden rule with web developing (see https://www.owasp.org/index.php/Input_Validation_Cheat_Sheet ), here is my code snippet to sanitise and parameterise MySQL queries in PHP 7.0.
First, watch and follow @jawache (Asim Hussein) demo how common hacks happen and why you should update and patch software often, sanitise user data and set up a firewall.
I have blogged before on how to set up a Vultr server and configure it, How to secure Ubuntu in the Cloud, How to run an Ubuntu System Audit and Beyond SSL with Content Security Policy, Public Key Pinning etc but a 100% secure server is impossible as zero-day exploits and flaws (e.g WPA WiFi) remind us how limited technology lifespans can be. Yes, you can setup firewalls on Ubuntu and WordPress but you are only one exploit away from being hacked. Below is my code snippet (in PHP) to sanitise incoming data, query a MySQL database with object-oriented calls in PHP 7.0 and return data in variables. I have set up a firewall to block access to MySQL and non-essential ports (use https://www.shodan.io/ to test your server’s ports). I was using older deprecated PHP 5 era database calls and I researched newer calls available in PHP 7.0.
When you log in to an Ubuntu server and it says the following you should update
Also update software, node, npm etc
This code outputs too much information but will help you setup and get data on your servers (as long as you replace your database, table and field names).
<?php
echo "Last modified: " . date ("F d Y H:i:s.", getlastmod()) . "<br /><br />";
date_default_timezone_set('Australia/Sydney');
$dbhost = '127.0.0.1';
$dbusername = 'themysqlaccount';
$dbpassword = 'themysqlpassword';
$dbname = 'thedatabasename';
$con = mysqli_connect($dbhost, $dbusername, $dbpassword, $dbname);
//Debug stuff
//echo var_dump($con);
//printf(" - Error: %s.\n", $stmt->error);
if($con->connect_errno > 0){
printf(" - Error: %s.\n", $stmt->error);
die("Error: Unable to connect to MySQL (E001)");
} else {
echo "Charset set to utf8<br />";
mysqli_set_charset($con,"utf8");
}
if (!$con) {
echo "Error: Unable to connect to MySQL (E002)" . PHP_EOL;
echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
exit;
} else {
echo "Database Connection OK<br />";
echo " Success: A proper connection to MySQL was made! The my_db database is great." . PHP_EOL . "<br />";
echo " - Host information: " . mysqli_get_host_info($con) . PHP_EOL . "<br />";
echo " - Server Info: '" . mysqli_get_server_info($con) . "'<br />";
echo " - Server Protocol Info : ". mysqli_get_proto_info($con) . "<br />";
echo " - Server Version: " . mysqli_get_server_version($con) . "<br />";
//echo " - Server Connection Stats: " . print_r(mysqli_get_connection_stats($con)) . "<br />";
echo " - Client Version: " . mysqli_get_client_version($con) . "<br />";
echo " - Client Info: '" . mysqli_get_client_info() . "'<br />";
echo "Ready to Query the database '$dbname'.<br />";
// Input Var's that are parameterized/bound into the query statement
// I pre fill three vaiables with known fields in my users table
// Goot article in manual sanitization of strings in PHP http://php.net/manual/en/filter.filters.sanitize.php
$in_username = mysqli_real_escape_string($con, 'FearTec');
$in_f_guid = mysqli_real_escape_string($con, '5161a571-4a51-468d-9e96-6a5db5d35b1c');
$in_mobile = mysqli_real_escape_string($con,'0456629533');
// Output Var's that the query fills after querying the database
// These variables will be filled with data from the current returned row
$out_id = "";
$out_f_guid = "";
$out_username = "";
$out_mobile = "";
echo "1. About to query the database: '$dbname'<br />";
$stmt = mysqli_stmt_init($con);
if (mysqli_stmt_prepare($stmt,"SELECT id, username, guid, user_mobile FROM thedatabasename WHERE username = ? AND guid = ? AND user_mobile = ?")) {
echo "2. Query Returned<br />";
/*
Type specification chars
Character Description
i corresponding variable has type integer
d corresponding variable has type double
s corresponding variable has type string
b corresponding variable is a blob and will be sent in packets
*/
mysqli_stmt_bind_param($stmt, 'sss', $in_username, $in_guid, $in_mobile);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $out_id, $out_username, $out_guid, $out_mobile);
mysqli_stmt_fetch($stmt);
// Do something with the 1st returned row
echo " - Row: ID: $out_id, GUID: $out_guid, USR: $out_username, MOB: $out_mobile";//
// Do we have more rows to process
while($stmt->fetch()) {
// Deal with other rows
echo " - Row: ID: $out_id, GUID: $out_f_guid, USR: $out_username, MOB: $out_mobile<br />";
}
mysqli_stmt_close($stmt);
echo "c<br />";
}
else {
echo "3. Error Querying<br/>";
printf(" - Error: %s.\n", $stmt->error);
}
}
?>
Returned Data
Last modified: November 01 2017 16:43:01. Charset set to utf8 Database Connection OK Success: A proper connection to MySQL was made! The my_db database is great. - Host information: 127.0.0.1 via TCP/IP - Server Info: '5.7.19-0ubuntu0.16.04.1' - Server Protocol Info : 10 - Server Version: 50719 - Client Version: 50012 - Client Info: 'mysqlnd 5.0.12-dev - 20150407 - $Id: b5########################
Variable Bind Parameter Types
When you bind an incoming variable you can inform MySQL what the data type is expected to be.
mysqli_stmt_bind_param: Type specification chars Character Description i corresponding variable has type integer d corresponding variable has type double s corresponding variable has type string b corresponding variable is a blob and will be sent in packets
Debug Options
Errors Enabled: Turn on PHP Debug Errors On
Turning on errors on production servers is bad but on development.
First, find php.ini files
Edit your appropriate PHP file
And turn on Error reporting.
Restart PHP and NGINX
If you need to view your active php.ini file or see PHP configuration settings, add this to a .php file on your web server and view its contents.
<?php phpinfo() ?>
It is amazing how clear errors can be
Dump Connection Vars: PHP mysqli_connect: var_dump($con)
echo var_dump($con);
Output:
public 'affected_rows' => int 0 public 'client_info' => string 'mysqlnd 5.0.12-dev - 20150407 - $Id: b5###############################3
Show Environment Vars: mysqli_get_host_info, mysqli_get_proto_info, mysqli_get_server_version, mysqli_get_client_version and mysqli_get_client_info.
echo " Success: Database connection OK." . PHP_EOL . "<br />"; echo " - Host information: " . mysqli_get_host_info($con) . PHP_EOL . "<br />"; echo " - Server Info: '" . mysqli_get_server_info($con) . "'<br />"; echo " - Server Protocol Info : ". mysqli_get_proto_info($con) . "<br />"; echo " - Server Version: " . mysqli_get_server_version($con) . "<br />"; //echo " - Server Connection Stats: " . print_r(mysqli_get_connection_stats($con)) . "<br />"; echo " - Client Version: " . mysqli_get_client_version($con) . "<br />"; echo " - Client Info: '" . mysqli_get_client_info() . "'<br />";
Output:
Success: Database connection OK. - Host information: 127.0.0.1 via TCP/IP - Server Info: '5.7.19-0ubuntu0.16.04.1' - Server Protocol Info : 10 - Server Version: 50719 - Client Version: 50012 - Client Info: 'mysqlnd 5.0.12-dev - 20150407 - $Id: b5########################################
Show errors in failed if statements: mysqli_stmt_prepare else
printf(" - Error: %s.\n", $stmt->error);
Output:
Error: Table 'thedatabasename.invalidtablename' doesn't exist.
Debugging is your friend.
More to come..
Donate and make this blog better
Ask a question or recommend an article
[contact-form-7 id=”30″ title=”Ask a Question”]
V 1.0 initial post
Short (Article):
Ready to Query the database 'thedatabasename'. 1. About to query the database: 'thedatabasename' 2. Query Returned - Row: ID: 1, GUID: 0000000-0000-0000-0000-000000000001, USR: Bob, MOB: 1234567890 - Row: ID: 2, GUID: 0000000-0000-0000-0000-000000000002, USR: Joe, MOB: 1234567891 - Row: ID: 3, GUID: 0000000-0000-0000-0000-000000000003, USR: Jane, MOB: 1234567892
Variable Bind Parameter Types
When you bind an incoming variable you can inform MySQL what the data type is expected to be.
Debug Options
Errors Enabled: Turn on PHP Debug Errors On
Turning on errors on production servers is bad but on on development.
First, find php.ini files
Edit your appropriate PHP file
And turn on Error reporting.
Restart PHP and NGINX
If you need to view your active php.ini file or see php configuration settings, add this to a .php file on your web server and view it’s contents.
It is amazing how clear errors can be
Dump Connection Vars: PHP mysqli_connect: var_dump($con)
Output:
Show Environment Vars: mysqli_get_host_info, mysqli_get_proto_info, mysqli_get_server_version, mysqli_get_client_version and mysqli_get_client_info.
Output:
Show errors in failed if statements: mysqli_stmt_prepare else
Output:
Debugging is your friend.
More to come..
Donate and make this blog better
Ask a question or recommend an article
[contact-form-7 id=”30″ title=”Ask a Question”]
V 1.0 initial post
Short (Article):
(length=79) public 'client_version' => int 50012 public 'connect_errno' => int 0 public 'connect_error' => null public 'errno' => int 0 public 'error' => string '' (length=0) public 'error_list' => array (size=0) empty public 'field_count' => int 0 public 'host_info' => string '127.0.0.1 via TCP/IP' (length=20) public 'info' => null public 'insert_id' => int 0 public 'server_info' => string '5.7.19-0ubuntu0.16.04.1' (length=23) public 'server_version' => int 50719 public 'stat' => string 'Uptime: 1828089 Threads: 1 Questions: 15702 Slow queries: 0 Opens: 1529 Flush tables: 1 Open tables: 1461 Queries per second avg: 0.008' (length=142) public 'sqlstate' => string '00000' (length=5) public 'protocol_version' => int 10 public 'thread_id' => int 7367 public 'warning_count' => int 0
Show Environment Vars: mysqli_get_host_info, mysqli_get_proto_info, mysqli_get_server_version, mysqli_get_client_version and mysqli_get_client_info.
Output:
Show errors in failed if statements: mysqli_stmt_prepare else
Output:
Debugging is your friend.
More to come..
Donate and make this blog better
Ask a question or recommend an article
[contact-form-7 id=”30″ title=”Ask a Question”]
V 1.0 initial post
Short (Article):
Ready to Query the database 'thedatabasename'. 1. About to query the database: 'thedatabasename' 2. Query Returned - Row: ID: 1, GUID: 0000000-0000-0000-0000-000000000001, USR: Bob, MOB: 1234567890 - Row: ID: 2, GUID: 0000000-0000-0000-0000-000000000002, USR: Joe, MOB: 1234567891 - Row: ID: 3, GUID: 0000000-0000-0000-0000-000000000003, USR: Jane, MOB: 1234567892
Variable Bind Parameter Types
When you bind an incoming variable you can inform MySQL what the data type is expected to be.
Debug Options
Errors Enabled: Turn on PHP Debug Errors On
Turning on errors on production servers is bad but on on development.
First, find php.ini files
Edit your appropriate PHP file
And turn on Error reporting.
Restart PHP and NGINX
If you need to view your active php.ini file or see php configuration settings, add this to a .php file on your web server and view it’s contents.
It is amazing how clear errors can be
Dump Connection Vars: PHP mysqli_connect: var_dump($con)
Output:
Show Environment Vars: mysqli_get_host_info, mysqli_get_proto_info, mysqli_get_server_version, mysqli_get_client_version and mysqli_get_client_info.
Output:
Show errors in failed if statements: mysqli_stmt_prepare else
Output:
Debugging is your friend.
More to come..
Donate and make this blog better
Ask a question or recommend an article
[contact-form-7 id=”30″ title=”Ask a Question”]
V 1.0 initial post
Short (Article):
Show errors in failed if statements: mysqli_stmt_prepare else
Output:
Debugging is your friend.
More to come..
Donate and make this blog better
Ask a question or recommend an article
[contact-form-7 id=”30″ title=”Ask a Question”]
V 1.0 initial post
Short (Article):
Ready to Query the database ‘thedatabasename’. 1. About to query the database: ‘thedatabasename’ 2. Query Returned – Row: ID: 1, GUID: 0000000-0000-0000-0000-000000000001, USR: Bob, MOB: 1234567890 – Row: ID: 2, GUID: 0000000-0000-0000-0000-000000000002, USR: Joe, MOB: 1234567891 – Row: ID: 3, GUID: 0000000-0000-0000-0000-000000000003, USR: Jane, MOB: 1234567892
Variable Bind Parameter Types
When you bind an incoming variable you can inform MySQL what the data type is expected to be.
Debug Options
Errors Enabled: Turn on PHP Debug Errors On
Turning on errors on production servers is bad but on on development.
First, find php.ini files
Edit your appropriate PHP file
And turn on Error reporting.
Restart PHP and NGINX
If you need to view your active php.ini file or see php configuration settings, add this to a .php file on your web server and view it’s contents.
It is amazing how clear errors can be
Dump Connection Vars: PHP mysqli_connect: var_dump($con)
Output:
Show Environment Vars: mysqli_get_host_info, mysqli_get_proto_info, mysqli_get_server_version, mysqli_get_client_version and mysqli_get_client_info.
Output:
Show errors in failed if statements: mysqli_stmt_prepare else
Output:
Debugging is your friend.
More to come..
Donate and make this blog better
Ask a question or recommend an article
[contact-form-7 id=”30″ title=”Ask a Question”]
V 1.0 initial post
Short (Article):
(length=79) public ‘client_version’ => int 50012 public ‘connect_errno’ => int 0 public ‘connect_error’ => null public ‘errno’ => int 0 public ‘error’ => string ” (length=0) public ‘error_list’ => array (size=0) empty public ‘field_count’ => int 0 public ‘host_info’ => string ‘127.0.0.1 via TCP/IP’ (length=20) public ‘info’ => null public ‘insert_id’ => int 0 public ‘server_info’ => string ‘5.7.19-0ubuntu0.16.04.1’ (length=23) public ‘server_version’ => int 50719 public ‘stat’ => string ‘Uptime: 1828089 Threads: 1 Questions: 15702 Slow queries: 0 Opens: 1529 Flush tables: 1 Open tables: 1461 Queries per second avg: 0.008’ (length=142) public ‘sqlstate’ => string ‘00000’ (length=5) public ‘protocol_version’ => int 10 public ‘thread_id’ => int 7367 public ‘warning_count’ => int 0
Show Environment Vars: mysqli_get_host_info, mysqli_get_proto_info, mysqli_get_server_version, mysqli_get_client_version and mysqli_get_client_info.
Output:
Show errors in failed if statements: mysqli_stmt_prepare else
Output:
Debugging is your friend.
More to come..
Donate and make this blog better
Ask a question or recommend an article
[contact-form-7 id=”30″ title=”Ask a Question”]
V 1.0 initial post
Short (Article):
Ready to Query the database ‘thedatabasename’. 1. About to query the database: ‘thedatabasename’ 2. Query Returned – Row: ID: 1, GUID: 0000000-0000-0000-0000-000000000001, USR: Bob, MOB: 1234567890 – Row: ID: 2, GUID: 0000000-0000-0000-0000-000000000002, USR: Joe, MOB: 1234567891 – Row: ID: 3, GUID: 0000000-0000-0000-0000-000000000003, USR: Jane, MOB: 1234567892
Variable Bind Parameter Types
When you bind an incoming variable you can inform MySQL what the data type is expected to be.
Debug Options
Errors Enabled: Turn on PHP Debug Errors On
Turning on errors on production servers is bad but on on development.
First, find php.ini files
Edit your appropriate PHP file
And turn on Error reporting.
Restart PHP and NGINX
If you need to view your active php.ini file or see php configuration settings, add this to a .php file on your web server and view it’s contents.
It is amazing how clear errors can be
Dump Connection Vars: PHP mysqli_connect: var_dump($con)
Output:
Show Environment Vars: mysqli_get_host_info, mysqli_get_proto_info, mysqli_get_server_version, mysqli_get_client_version and mysqli_get_client_info.
Output:
Show errors in failed if statements: mysqli_stmt_prepare else
Output:
Debugging is your friend.
More to come..
Donate and make this blog better
Ask a question or recommend an article
[contact-form-7 id=”30″ title=”Ask a Question”]
V 1.0 initial post
Short (Article):