I am currently validating a POST value using a REGEX and a SWITCH, it’s works perfectly but isn’t really scalable as it means manually editing the PHP file when new values are added to the form. Instead I have decided to create a function which calls a MySQL database to determine if the POST value is valid or not.
// Validate flag value
function ValidateFlagValue($FlagName, $FlagValue) {
$stmt_query = "SELECT flag_name FROM flags WHERE flag_name = ? AND flag_min >= ? AND flag_max <= ? LIMIT 1";
// Prepate statement
mysqli_stmt_prepare($stmt, $stmt_query);
// Bind parameters
mysqli_stmt_bind_param($stmt, "sii", $FlagName, $FlagValue, $FlagValue);
// Execute statement
mysqli_stmt_execute($stmt);
// Store results
mysqli_stmt_store_result($stmt);
// Count results
if (mysqli_stmt_num_rows($stmt) == 1 ) {
return true;
} else {
return false;
}
// Close statement
mysqli_stmt_close($stmt);
}
Is this the right way to go about this?
And have I written the function correctly?
I don’t see where you’re passing in the POST value. You’re naming it “flag_” ?
Also note that “return” causes the script to exit so any code after that won’t run.
In this case, the script exiting will close things down anyway, but something to keep in mind.
There is one critical problem and two improvements that can be made.
The problem is that nowhere $stmt is defined. You have to create it out of $mysqli instance that have to be added to function parameter list.
First improvement belongs to SQL where you can use BETWEEN clause and thus avoud binding the same variable twice.
Another one is based on the extreme verbosity of raw mysqli API. I strongly recommend you to use PDO instead. Look how dramatically shorter your code could be with PDO:
// Validate flag value
function ValidateFlagValue($pdo, $FlagName, $FlagValue)
{
$sql = "SELECT 1 FROM flags WHERE flag_name = ? AND ? BETWEEN flag_min AND flag_max LIMIT 1";
$stmt = $pdo->prepare($sql);
$stmt->execute([$FlagName, $FlagValue]);
return $stmt->fetchColumn();
}
PDO not only lets you avoid manual binding at all, but also have a very handy function to return the query result at once.
So again, consider PDO instead of mysqli. Give PDO a try and you will see how powerful it can be.
The array passed to the execute is being manually bound as strings. If you want to bind something that isn’t a string you still need separate bind statements.
For musql they are the same. The only difference for the “b” type in mysqli and param_lob in PDO is that data being sent to the server in packets. For such a case when you need to send a string that size exceeds max_allowed_packet, seperate binding is the only way, I have to admit.
So how would you go about storing images IN the database then (which is essential once you get a larger system using transactions) if you don’t store them as blobs?
I think we’re a little bit off topic now but thanks for helping.
I have stayed well clear of OOP for 7 years because I simply don’t understand it. However given that I want to learn C# in the coming few years I’m going to give it another try. I will write out some OOP PHP and PDO and come back once I have a noddy script.
Keep in mind that you don’t need to know how to write OOP to use it.
True, a basic understanding helps and knowing the syntax is essential, but there are a lot of OOP classes you can use now without needing to fuss much.
I suggest you turn on error reporting and display and give it a try (in your dev environment only please). The error messages will help you to learn what needs to be done.
Needs to be $dsn = 'mysql:dbname=dev;host=127.0.0.1';
Don’t replace dbname because that is how you connect to your database. It’s equivalent to procedural mysqli_*'s prepare($db, ..) where $db is the database you want to use.
Also, you should take note that square bracket arrays [] are available only from PHP 5.4 +. So don’t use square bracket arrays [] if you have PHP 5.3. You’ll get an error. Use array(); as an alternative if you have PHP 5.3 and below.
The reason why they are passing the $pdo/db variable is because in PHP, you don’t really want to create a new database connection every time you do a query. It’s easier to re-use the connection then closing it and re-opening it. In a way, it’s kind of like how system admins are with up-time.
You don’t need to. You can still use OOP mysqli_*.
<?php
// Connect to MySQL
$host = '127.0.0.1';
$username = 'dev';
$password = 'dev.dev';
$db = 'dev';
$mysqli = new mysqli($host, $username, $password, $db);
if($mysqli->connect_errno) {
die('Do not display any errors here. You should NEVER display errors to the user.');
}
$sql = 'SELECT 1 FROM flags WHERE flagname = ? AND ? BETWEEN MIN(flagvalue) AND MAX(flagvalue) LIMIT 1';
$prepare = $mysqli->prepare($sql);
$prepare->bind_param('ss', $flagname, $flagname2);
$prepare->execute();
$prepare->store_result();
if($prepare->num_rows) {
$prepare->bind_result($first_flag);
while($prepare->fetch()) {
print($first_flag);
}
} else {
print('No record with that information');
}
Also take note, you should always be using num_rows and rowCount() as it helps prevent white pages (nothing is displayed, no errors, no warning, nothing) and prevents error warning. It is also helps better user experience.
<?php
// Connect to MySQL
$dsn = 'mysql:dbname=dev_pof;host=127.0.0.1';
$username = 'dev';
$password = 'dev.dev';
$pdo = new PDO($dsn, $username, $password,[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
// Function to validate the flag value
function CheckFlagValue($pdo, $FlagName, $FlagValue)
{
$sql = "SELECT 1 FROM flags WHERE flagname = ? AND flagvalue = ? LIMIT 1";
$stmt = $pdo->prepare($sql);
$stmt->execute([$FlagName, $FlagValue]);
return $stmt->fetchColumn();
}
// Function to get the flag description
function GetFlagDescription($pdo, $FlagName, $FlagValue)
{
$sql = "SELECT flagdescription FROM flags WHERE flagname = ? AND flagvalue = ? LIMIT 1";
$stmt = $pdo->prepare($sql);
$stmt->execute([$FlagName, $FlagValue]);
return $stmt->fetchColumn();
}
echo GetFlagDescription($pdo, 'intent', 9);
?>
The GetFlagDescription function is working and shows the flag description when the flag name and value are passed.
However CheckFlagValue I need this function to return true or false depending on the row count. There doesn’t seem to be a row count function for PDO??
rowCount needs to be rowCount();
It has to be after the ->execute() or it won’t work. Also, it’s best to check if the row actually exists for all your query calls. Let’s say you only have 2 rows of data in your database and someone typed in their address bar ?flag=3. What then? It’ll display a big white blank page and nothing else. If you don’t handle errors the right way, your users will question, “Does this webmaster know what he’s doing because the page shows nothing?”
It’s best to handle errors the proper way so that users don’t keep sending feedbacks about blank pages. This is also a way for hackers to hack your website. If your page crashes after a certain amount of data, they know that those data don’t exist and they can exploit it. So rather than giving them a free pass, you should handle your errors the right and proper way by using num_rows (mysqli_*) and rowCount() (PDO).
