Coming from MySQL 5

[AdmiralQ]

Greetings. So I'm coming from the last 12 years using MySQL 5. I'm rewriting my site from scratch and have updated to PHP 8 and MySQL 8. If I could get a couple of examples to query a dB for a single row using PDO and then again using mysqli I would greatly appreciate it. Before now I would have written it as such:

$query = "SELECT * FROM ember_features WHERE id = '4'";
$result= mysql_query($query) or die("Could not perform query: ".mysql_error());
$row = mysql_fetch_array(result);

I have already successfully opened a link to the dB using:

$db_host = "localhost";
$db_name = "jandrews_emberedutech";
$db_username = "********";
$db_pword = "********";

$dsn = "mysql:host=$db_host;dbname=$db_name";

try {
	$db_connection = new PDO($dsn, $db_username, $db_pword);
	$db_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
	//echo "Connection successful<br>";
} catch (PDOException $error){
	echo "Error: ".$error->getMessage()."<br>";
	echo "Code: ".$error->getCode()."<br>";
	Die("Oops. Something went wrong in the dB.");
}

So I was hoping to get two working examples from this simple task that I could take and modify for my purposes ... one for the PDO method and the other for the mysqli method. THANK YOU!

[mac_gyver]

PDO examples -

 a non-prepared query. assuming the literal 4 (which is not a string, it's a number) is hard-coded in the query -

$query = "SELECT * FROM ember_features WHERE id = 4";
$stmt = $pdo->query($query);
$row = $stmt->fetch();

a prepared query. assuming the value being shown in this query is a dynamic value -

$query = "SELECT * FROM ember_features WHERE id = ?"; // a simple ? place-holder where the dynamic value is used at in the query
$stmt = $pdo->prepare($query); // prepare the query
$stmt->execute([ 4 ]); // execute the query, suppling dynamic value(s) as an array to the execute() call
$row = $stmt->fetch();

where did you find this connection code? here's everything that's wrong with it -

1. it's not setting the character set to match your database tables, so character conversion can occur, breaking multiple-byte characters
2. it's not using exceptions for errors, which is the default setting now, which simplifies all the error handling
3. it's not using true prepared queries. by using emulated prepared queries (the default) and not setting the character set, your queries are open to sql special characters in a value being able to break the sql query syntax, which is how sql injection is accomplished
4. it's not setting the default fetch mode to assoc, so you will need to specify the fetch mode in each fetch statement
5. there's no point in catching and handling a connection error in your application since a user to your site can do nothing about it and doesn't need to know it is the reason a page isn't working. catching and outputting the raw error information also helps hackers when they intentionally trigger connection errors (by flooding your site with requests that consume all the database connections.) if you have ever looked at a connection error, it lists the database server host/ip address, the database username, if the connection is using a password, and web server path information. do you really want to give someone this much information about your site? you should only catch and handle user recoverable database errors in your application, such as when inserting/updating duplicate user submitted data. for all other query errors and all other type of queries, simply do nothing in your code and let php catch hand handle database errors.

[AdmiralQ]

I got that dB open link code from a video on YouTube. I'll make the modifications you mentioned.

I'm curious, do you have a preference between the non-prepared and the prepared, because I can follow the logical steps of the unprepared, but the prepared method is bit greek to me. I suspect you're going to tell me the prepared method is better and more secure (of course, because it's the more confusing one to me) so I just need to make myself okay with that. I think I can put it together. It's just so different from what I'm use to. THANK YOU for your response!

[AdmiralQ]

I'm getting the following error messages: 

Warning: Undefined variable $pdo in /home/jandrews/public_html/admin_authenticate.php on line 12
Fatal error: Uncaught Error: Call to a member function prepare() on null in /home/jandrews/public_html/admin_authenticate.php:12 Stack trace: #0 {main} thrown in /home/jandrews/public_html/admin_authenticate.php on line 12

 

That's the line with: $stmt = $pdo->prepare($query);

<?
session_start();
extract($_REQUEST);

$fname = "";
$lname = "";


include 'r_open_link.php';

$query = "SELECT * FROM ember_features WHERE id = ?";
$stmt = $pdo->prepare($query);
$stmt->execute([4]);
$row = $stmt->fetch();

//	Testing accurate dB data readout
print "<br><br>";
print "First name: ".$row['fname']."<br>";
print "Last name: ".$row['lname']."<br>";
?>

I'm afraid being used to PHP 5 I also suck at understanding defining variables. When I added $pdo = ""; after $lname = ""; the undefined variable error went away but not the other one.

[AdmiralQ]

Nevermind all that. As soon as I switched to YOUR open connection code the script worked. You had given me a sample connection code on another thread last week, but I had already found something that seemed to work. It was obviously seriously flawed. When I switched to yours this problem went away. THANK YOU!

[mac_gyver]

5 hours ago, AdmiralQ said:

a preference between the non-prepared and the prepared

it's a matter of functionality/purpose, not preference. a non-prepared query is used when there are no dynamic values being supplied to the query. a prepared query is used when you are supplying dynamic values to the query, in order to separate the parsing of the sql query syntax, from the evaluation of the values during the execution of the query. this is so that values like didn't or O'tool, which contain an sql special character, cannot break the sql query syntax, which is how sql injection is accomplished.

in one of your previous threads i gave detailed instructions on converting an sql query with php variables being put directly into it, into a prepared query.

 

[maxxd]

This is a little late, but note that in addition to using the simple '?' placeholder you can use named placeholders like so:

$query = "SELECT * FROM ember_features WHERE id = :feature_id";
$stmt = $pdo->prepare($query); // prepare the query
$stmt->execute(['feature_id' => 4]); // execute the query, suppyling dynamic value(s) as an associative array to the execute() call
$row = $stmt->fetch();

Admittedly pointless in the query above, but when you get more placeholders in place it's easier to keep track of them using names.