Help with changing from MSQ to PDO

[WAMFT1]

As I am VERY new to PDO, can someone help me in converting the below 2 codes so that I can setup the testing site knowing at least this part works. Below is the old SQL that I need to convert but don't have to foggiest on where to start.  If I can get some help with this I can slowly work through the rest of my site with a bit more ease.

LOGIN CODE:

<?php
		if($_POST['submit']){
			$Username = protect($_POST['Username']);
			$Password = protect(sha1($_POST['Password']));
			if(!$Username || !$Password){
				echo "<center>Please enter your <b>Username</b> and <b>Password</b>!</center>";
			}else{
				$res = mysql_query("SELECT * FROM `eusers` WHERE `Username` = '".$Username."'");
				$num = mysql_num_rows($res);
				if($num == 0){
					echo "<center>The <b>Username</b> or <b>Password</b> you supplied is incorrect!</center>";
				}else{
				$res = mysql_query("SELECT * FROM `eusers` WHERE `Username` = '".$Username."' AND Password = '".$Password."'");
				$num = mysql_num_rows($res);
					if($num == 0){
						echo "<center>The <b>Password</b> you supplied is incorrect!</center>";
					}else{
						$row = mysql_fetch_assoc($res);
						if($row['Active'] != 1){
							echo "<center>Your login has been <b>deactivated</b>, Please contact your Manager for assistance.</center>";
						}else{
							header('Location: secure.php');
							$time = date('U')+7200; //2 Hours
							mysql_query("UPDATE `eusers` SET `Online` = '".$time."' WHERE `id` = '".$_SESSION['uid']."'");
							$_SESSION['uid'] = $row['id'];
 						}}}}}
?>

ACCESS GRANTED (Each Page):

<?php
		if(strcmp($_SESSION['uid'],"") == 0){
			printf("<script>location.href='index.php'</script>");
		}else{
			$time = date('U')+7200; //2 Hours
			$update = mysql_query("UPDATE `eusers` SET `Online` = '".$time."' WHERE `id` = '".$_SESSION['uid']."'");
		}
?>

 

[mac_gyver]

the database extension is responsible for providing the interface between your application code and the database server. it is used to make the connection, execute queries, and fetch/test the result from queries. so, updating old code to use a new and different database extension would involve replacing the existing connection, query, fetch, and result-test statements with the equivalent statements from the new extension. 

the PDO extension brings two things to php that eliminates a lot of old implementation logic, 1) prepared queries, when supplying external, unknown, dynamic data values to the query when it gets executed, and 2) exceptions for errors.

a prepared query is a one that has a simple ? place-holder in it for each data value, the query is then prepared, as a separate step, and is supplied the actual data values when the query gets executed. all the extra code/syntax you may have now that's trying to make each different type of data safe and get the values into the sql query statement are removed. for your posted code, that would eliminate the protect() calls and eliminate all the extra quotes and concatenation dots around each variable in the sql query. for the UPDATE query you have shown, it would look like this -

// build the sql query in a php variable. this helps when debugging, you can echo the query, it separates the sql syntax from the php syntax as much as possible, reducing typo mistakes, and will lead to further simplification of the code (common/repetitive logic can be consolidated)
$sql = "UPDATE eusers SET Online = ? WHERE id = ?";
// prepare the query - this sends the sql query statement to the database server, where it will be checked for syntax errors and the query execution will get planned 
// $pdo in the following is the name of the variable holding the instance of the PDO database connection
$stmt = $pdo->prepare($sql);
// execute the query, supplying the variables that were removed from the sql query statement as an array.
$stmt->execute([ $time, $_SESSION['uid'] ]);

// for an INSERT, UPDATE, or DELETE query, use the following to get/test the number of affected rows -
$affected_rows = $stmt->rowCount();

 

for a non-prepared query, all types - SELECT, INSERT, UPDATE, DELETE, you would just use the query() method, instead of the prepare()/execute() calls.

for a SELECT query, you would then use one of the available fetch methods to retrieve the data.

// for a query that will match at most ONE row of data, use the following to fetch a single row -
$var = $stmt->fetch();

// for a query that will match a set of zero or more rows of data, use the following to fetch all the rows at once -
$var = $stmt->fetchAll();

// the rowCount() method isn't available for all database types that PDO can be used with.
// the universal way of testing if or how many rows of data a query matched is to just fetch the data and test the fetched result (a true value means there is data) or use the php count() function to get the number of rows of fetched data.

// for the above two fetch... examples, to just test if the query matched any data -
if($var)
{
	echo "the query matched some data. var contains the data from the query."; 
}

// if you actually want a count of the number of rows a query matched
$num_rows = count($var);
echo "the query matched $num_rows rows.";

 

next, exceptions for errors. you ALWAYS need error handling for any statement that can fail. for database statements, if you use exceptions for errors and in most cases let php catch and handle the exception, php will use its error related settings to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.) the exception to this rule are for errors that are 'recoverable' by the visitor to your site. these would be things like inserting/updating duplicate or out of range values. in this case, your code would catch the exception, test if the error number is for something that your code is designed to handle, then setup a message for the visitor telling them what was wrong with the data that they submitted. if the error number is for anything else, just re-throw the exception and let php handle it.

lastly, the following is typical PDO connection code that shows setting some common options -

$DB_HOST = ''; // database host name or ip address
$DB_USER = ''; // database username
$DB_PASS = ''; // database password
$DB_NAME = ''; // database name
$DB_ENCODING = 'utf8'; // db character encoding. set to match your database table(s) character set

$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // set the error mode to exceptions
		PDO::ATTR_EMULATE_PREPARES => false, // run real prepared queries
		PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC // set default fetch mode to assoc
	];

$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS,$options);

 

this should cover everything needed to address the database statements in the posted code. i'll let others comment on what your code is doing that it should do differently.

Edited June 9, 2020 by mac_gyver