To Convert or To Not Convert to PDO?

[phreak3r]

Hi there PHPFreaks! Phreak3r here, back again with another question. This is just code from one of the main scripts/files that I am working with. I would just like to know if I should go ahead and convert to PDO before I even continue writing more code or not. I have been told that this mess is ripe for SQL injection attacks.

<?php 
include('header.php');
require('dbcon/dbcon.php');

if ($_SERVER['REQUEST_METHOD'] == 'POST') {

	// sanitize values before entering them into db, no bad seeds.
	$username = mysqli_real_escape_string($conn, $_POST['username']);
	$password = mysqli_real_escape_string($conn, $_POST['password']);
	$hashed_password = mysqli_real_escape_string($conn, password_hash($password, PASSWORD_DEFAULT));
	$email = mysqli_real_escape_string($conn, $_POST['email_address']);
	$confirmation_status = 0;
	
	$account_open_date = date("Y-m-d h:i:s");
	$current_date = date("Y-m-d h:i:s");
	$account_open_date_retrieval_sql_select = "SELECT account_open_date from profile0";
	$account_age = date_diff($row, $current_date); // acct open date - current date = account age
	$account_age_result = mysqli_query($conn, $account_open_date_retrieval_sql_select);
	$row = mysqli_fetch_assoc($account_age_result);

	$sqlinsert = "INSERT INTO profile0 (username, password, email_address, confirmation_status, account_open_date, account_age) VALUES ('$username', '$hashed_password', '$email', '$confirmation_status', '$account_open_date', '$account_age')";
	$result = mysqli_query($conn, $sqlinsert);

	/*if (!$result) {
		die('Could not enter data!' . mysqli_error($conn));
	}*/
}

P.S. Any and all resources and opinions are welcomed! I am new to PDO and have been reading up on some off it. Although, I do not want to continue any bad habits or practices.

[benanamen]

It's never too soon to start doing things correctly. If you are able, go ahead and get down on PDO and rewrite your script. Here is a tutorial to get you going.

 

https://phpdelusions.net/pdo

[phreak3r]

It's never too soon to start doing things correctly. If you are able, go ahead and get down on PDO and rewrite your script. Here is a tutorial to get you going.

 

https://phpdelusions.net/pdo

 

Thank you for the link! MySQLi is nice, but there are still issues with it. I think PDO may help with developing better practices and is more secure.

[kicken]

The best way to prevent SQL injections is to use parameter binding. It's possible to do this with mysqli, but it's complicated and cumbersome. PDO on the other hand makes it super easy so there's no excuse for not doing it.

 

As a side benefit, PDO makes it slightly easier to swap to a different database engines if necessary. You'd still have to update your queries but the code to execute them and process the results should remain the same.

[mac_gyver]

if you switch to the php PDO extension, use prepared queries when supplying data to the sql query, and use exceptions to handle database errors (connection, prepare, execute), you will end up with the least amount of code.

 

the PDO extension is simpler and more consistent than the mysqli extension.

 

real prepared queries (PDO has emulated prepared queries which are open to sql injection if the character set php is using is not the same as your database tables) are the simplest and surest way of preventing sql special characters in the data from breaking the sql syntax (which is how sql injection is accomplished). prepared queries also eliminate any use of xxxx_escape_string() statements, simplifying the code or reducing the amount of code you have to modify. they also simplify the sql query syntax, since the php variables, any quotes around the variables, and any concatenation dots are removed from the sql query syntax and replaced with a place-holder, one for each value being put into the query.

 

by using exceptions to handle database errors, you eliminate any discrete error handling logic in the code, simplifying the code or reducing the amount of code you have to modify. to use exceptions, all you have to do is set the error mode to exceptions when you make the database connection and let php catch any exception, where it will use its error_reporting, display_errors, and log_errors settings to control what happens with the actual error information. when learning, developing, and debugging code/queries, you would display all errors and when on a live/public server, you would log all errors, simply by changing php's display_errors and log_errors settings (error_reporting should always be set to E_ALL.)

 

also, OOP syntax is shorter then procedural, so there's less typing and clutter in the code. any php errors that are thrown for OOP code, halt execution, rather than to continue and output secondary follow-on errors as is the case with the procedural mysqli functions.

 

so, simpler code and queries, secure queries, and error handling that won't output the raw error information on a live site (or require you to comment it out when changing the environment the code is running in.)

 

btw - within the context for any particular sql query, there's no need for verbose variable names like $account_open_date_retrieval_sql_select or $sqlinsert. just use simple variable names like $sql for the sql statement and $stmt for the pdo statement object, then fetch the result from the query into a specifically named variable that indicates the meaning of the data. you should completely deal with forming, preparing (if data is being supplied to the sql statement), executing, and getting the result from any query before moving onto the next query.

Edited January 21, 2018 by mac_gyver