Procedural PHP Prepared Statements

[Dysania]

Hey everyone! I've made a number of sites for myself, but I've decided to work on a pet/avatar site and I'm just getting into it. Generally on my own sites I've used

mysqli_real_escape_string

as one of the checks before putting user-input into the database, but I want to transition over to using prepared statements instead. I know those are much more secure. I tried to follow a tutorial I found, but when I tried I got an error saying:

Fatal error: Uncaught Error: Call to undefined function mysqli_stmt_get_result()...

I think I found a way around that, but honestly I'm a little lost with the whole prepared statements thing. I like to code exclusively in procedural style (as I came from mysql and it was just an easier transition and now I'm kind of set in my ways). Can someone walk through procedural prepared statements as if it were the first time hearing about it? I'd really appreciate it!!!

[Dysania]

Honestly, I don't understand because using prepared statements is SO much more work than using mysqli_real_escape_string... and if you even change the database you will have to go back to each statement and fix it... What am I missing?

[el-Relente]

Since no one has replied, i thought I'd just jump in and point you in a helpful direction

Latest secure database class created by @judda (owner of TGL):

https://github.com/awjudd/pdo-wrapper

 

Read through the code in /src/.

Your code would then end up something like:

$config = Configuration::fromArray(["Hostname":"localhost","Database":"","Username":"","Password":""]);
$this -> db = new Database($config);

 

A basic query would look something like this (although you'd probably do some more validation to ensure you're passing an actual value into it):

$user_id = $_SESSION['user_id'];
$myResult = $this -> db -> query('SELECT username,email FROM users WHERE id={0:ud}', $user_id);

if($myResult -> numberOfRows == 1){
	print "Logged in as user " . $myResult -> getArray()['username'];
}

{0:ud} the zero means the first parameter after the query (in this case $user_id). 'ud' represents an unsigned decimal (ie, a non-negative number) If you were using a string you would us "s" instead of "ud". Read the file /src/Database/ValueType.php for more info on the different types. If the value handed in doesnt match the type expected in the query an error with be thrown.

Lastly if you were doing a query that yields multiple results, you could iterate through it using something like:

$myResult = $this -> db -> query('SELECT id,username FROM users ORDER BY id ASC");

foreach($myResult -> retrieveAllRows() as $key = >$value){
	print "User ID #".$value['id'].' is username '.$value['username'].'<br />';
}

 

Disclaimer: None of this code is tested, but theoretically should work.

Edited April 10, 2020 by el-Relente

[judda]

Man that library brings back some great memories I haven't touched it in years but yeah it's pretty good at wrapping the complexity (and still showing it) of prepared statements in PHP

On 4/9/2020 at 10:26 PM, Dysania said:

Hey everyone! I've made a number of sites for myself, but I've decided to work on a pet/avatar site and I'm just getting into it. Generally on my own sites I've used

mysqli_real_escape_string

as one of the checks before putting user-input into the database, but I want to transition over to using prepared statements instead. I know those are much more secure. I tried to follow a tutorial I found, but when I tried I got an error saying:

Fatal error: Uncaught Error: Call to undefined function mysqli_stmt_get_result()...

I think I found a way around that, but honestly I'm a little lost with the whole prepared statements thing. I like to code exclusively in procedural style (as I came from mysql and it was just an easier transition and now I'm kind of set in my ways). Can someone walk through procedural prepared statements as if it were the first time hearing about it? I'd really appreciate it!!!

Does your install have the mysql library installed? What version of PHP are you using?

[Dysania]

I FINALLY figured out my issues!!! I just learned today that my host (Hostgator) doesn't have the mysqlnd extension unless you upgrade to the more expensive hosting. Well, guess I'll have to make some changes...

[judda]

Ugh - don't bother upgrading with them service is terrible and they try to charge you for absolutely everything even stuff they really shouldn't!

[Dysania]

I'm not going to. My husband and I decided to just use his server that he uses for Plex.