Hello Freaks, Geeks & Misfits

[MrMagic909]

MrMagic's the name and Magic is the game.

I used to do a bit of php coding back in the day but came to find that with PHP8 things have changed quite a bit, so i'm here to learn and pick up a few new tricks.

I'm writing a script for a website and need some insights into the new way of working in php8. I want to make a robust and secure script that incorporates all the latest security mechanismes to prevent sql injections and all the other bad stuff

Looking forward to meeting and talking to you all, well not all that would be a bit much but you get my drift, right ?

Adios

[Barand]

Welcome.

2 minutes ago, MrMagic909 said:

mechanismes to prevent sql injections

You'll be wanting to look at prepared statements then. Take the PDO route and not mysqli - it's much better.

[MrMagic909]

17 hours ago, Barand said:

Welcome.

You'll be wanting to look at prepared statements then. Take the PDO route and not mysqli - it's much better.

Yes indeed, i did start to write some stuff using prepared statements, but through mysli. Can you explain a bit more about why PDO is better in this case?

[MrMagic909]

and thank you for the warm welcome!

[Barand]

My main reason is that PDO is consistent.

• In mysqll, when you use the query() method it produces a result object; when you use prepare() it produces a statement object. It looks like mysqli was developed by two teams who never spoke to ane another as the methods within these two objects are completely different, so you have to process results differently.
• In PDO you always get a statement object so processing is always the same.

The PDO methods are cleaner to use for prepared statements.

PDO can be used with many different RDBMS, so if you want to change, the php remains constant (although you may have to adjust the SQL dialect in your queries).

Did you read the link I gave you?

[MrMagic909]

Hey Barand,

hahaha funny that you express it that way My understanding is that it has to do with two basically different approaches, nr 1  the procedural coding and 2 the object oriented coding method ?

but I have to agree that for some purposes one method would be better than the other. I will probably try to mix the both ways together, seeing as I am quite the scatterbrain when I get into coding loool

Got any good reference materials for doing it PDO style ?? ooops sorry didnt see the link before got it now, I will go study

thanks mate

[mac_gyver]

forget about using the overly complicated and inconsistent mysqli extension. some additional problems -

1. the procedural and OOP notation have different error responses for the same problem. things that should be and are fatal errors with OOP notation, are just warnings using procedural notation, and the procedural code continues to run, resulting in follow-on errors, not directly related to the problem.
2. some of the features that were added to the mysqli extension, to try to make it more usable, are dependent on the mysqlnd driver being used (and they just corrected one of these but not the others.) if you don't manage your php build, you cannot guarantee that the mysqlnd driver will be used, resulting in code that is not portable between php installations.
3. whoever defined the mysqli result fetch_all default fetch mode didn't know what they were doing. the fetch mode refers to the rows of data in the 'all' array, not the all array indexes. the default fetch mode for every other fetch statement is BOTH. the default fetch mode for the fetch_all statement is numeric, meaning you won't get associative indexes for the rows of data unless you explicitly set the fetch mode in the fetch statement.

when you use the PDO extension -

1. set the character set to match your database table's character set, so that no character conversion takes place over the connection. (you should do this for all database extensions, but it is rarely done.)
2. set the error mode to exceptions, so that all the statements that interact with the database server use exceptions (this is now the default setting in php8+ for both the PDO and mysqli extensions.)
3. set emulated prepared queries to false. you want to use real prepared queries whenever possible.
4. set the default fetch mode to assoc, so that you don't need to specify it in each fetch statement.
5. when using prepared queries, use implicit binding, by simply supplying an array to the ->execute([...]) call.

 

Edited June 28, 2023 by mac_gyver

[MrMagic909]

That is a buttload of intel to process bruddha @mac_gyver hhh Like I said i've been away from coding for a while so there's tons of stuff I gotta catch up on and your hints will def help smooth the path. Could you direct me to some intel on how to perform the actions you've mentioned, that would be of great help.

And I do agree the mysql has always been inherently flawed even way back in plain mysql days, and as often is the case they only try to "repair" the flaws with additional flawed methods.

So it's becoming painfully clear I have to step away from my habit of using MySQL and adopt to better protocols for handling databases.

thanks

[mac_gyver]

typical PDO connection code -

$DB_HOST = ''; // database host name or ip address
$DB_USER = ''; // database username
$DB_PASS = ''; // database password
$DB_NAME = ''; // database name
$DB_ENCODING = 'utf8mb4'; // db character encoding. set to match your database table's character set. note: utf8 is an alias of utf8mb3/utf8mb4

$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // set the error mode to exceptions (this is the default setting now in php8+)
			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);

 

php only sends two things to the database server when using prepared queries, 1) the sql query statement, with (optional) positional ? place-holders in it, and 2) an execute command, with an (optional) list of values corresponding to the place-holders. when using true prepared queries, supplying an array to the ->execute([...]) call, preserves the data type of the values, so, this works correctly for strings, numbers, boolean, null, and blob data (when it is less then the max packet size setting.)

typical prepared query -

$sql = "some sql statement with positional ?, ... place-holders in it where values are to be evaluated when the query is executed";
$stmt = $pdo->prepare($sql);
$stmt->execute([ a_value_for_each_place-holder, ... ]);
// for queries which return result sets, fetch the data here
// see the fetch(), fetchAll(), and fetchColumn() methods
// php also has some useful fetch modes, which can do things like pivot/index data by the first column selected, return a single-column one-dimensional array when using fetchAll(), ...

 

you are likely to see example code posted on the web that is using exception try/catch logic around all the database statements that interact with the database server - connection, query, exec, prepare, and execute. ignore this. it is unnecessary clutter that does nothing useful. the only time your code should catch and handle a database exception is for user recoverable errors, such as when inserting/updating duplicate or out of range data. in these cases, you would have try/catch logic (specifically dealing with the ->execute() call), test if the error number is for something that your code is designed to handle, and setup an error message for the user letting them know what was wrong with the data that they submitted. for all other error numbers, just re-throw the exception and let php handle it. in all other cases, simply do nothing, and let php catch and handle the database exceptions, where php will use its error related settings to control what happens with the actual error information, via an uncaught exception error (database statement errors will 'automatically' get displayed/logged the same as php errors.)

[MrMagic909]

Niice bruddha thanks for setting me up with with this sweet info, it will help me get to where I need to go much faster !!

I will go over all that has been "spilled" to me here and adjust my code accordingly. Luckily I only came as far as the basics in writing the script, just  registration/activation/login with all the neccesary checks for clean data and security ofcourse.

If I have any issues in rewriting I will for sure be back to consult with you guys ...

Thanks again y'all, much appreciated

[MrMagic909]

Okay i've started diving in and already found that I was getting way ahead of myself with just connecting to the database ... sooooo .... here's a few questions I gotta / like to ask :

First off, I am used to creating functions for certain tasks and putting them in a functions.php, now i've done stuff with classes in the past too ...  so what is the best way to go when using the PDO style ?

Also I noticed I had created a db_connect.php that I include, but I also created a function to connect and close a database ... is this wise, and if not what is the best way to go about this ?

already stuck .. after  just an hour lol

or should I ask these questions in another board?

 

[Barand]

2 minutes ago, MrMagic909 said:

should I ask these questions in another board?

Yes