MySQL PDO Statement not working

[greenreader9]

Ok.

I have a bunch of SQL statements in mysqli, but apparently that is not good enough with SQL injection (And if it is (please let it be), how would I make it safe?).

Anyways, I am moving things to PDO. Apparently I absolutely suck at it.

My code:

<?
require __DIR__.'/Connect.php';



if(isset($_POST['submit'])){


$demovariable = "194";
$sqlQuery = "SELECT * FROM hosting_domains WHERE domain_id =?";
$stmt = $connect->prepare($sqlQuery);
$stmt->bind_param('s', $demovariable);
$sql = $stmt->execute();
if ($sql) {
    echo "yes";
}else{
    echo "nope";
}


$DomainResult = $sql->fetch();  //Hello line 21, Yep, this guy is the the issue (I think)
echo $DomainResult['domain_key'];


...

And it spits the below back at me

yes
Fatal error : Uncaught Error: Call to a member function fetch() on bool in /path/to/file/File.php:21 Stack trace: #0 {main} thrown in /path/to/file/File.php on line 21

Thanks!

[greenreader9]

Wait a second.

Is the Connect.php the issue? I think it’s still setup for mysqli…

---

<?php
ob_start();
if(session_status() == PHP_SESSION_NONE){
	session_start();
}
require __DIR__."/../modules/Database/Config.php";

if(!isset($connect)){
	$connect = mysqli_connect(
		$DataBase['hostname'],
		$DataBase['username'],
		$DataBase['password'],
		$DataBase['name']
	);
	if(!$connect){
		echo 'Connection not established'; 
	} 
}

?>

Yep. Maybe that is the root cause?

[benanamen]

This tutorial will get you going.

(The only proper) PDO tutorial

[SamA74]

@greenreader9

Maybe that is the root cause?

Certainly.
You need to make a PDO connection to work with PDO.

@greenreader9

And if it is (please let it be), how would I make it safe?

You can use prepared statements with parameter binding in mysqli to guard against injection. But…
I would strongly advise to make the move to PDO. It may seem difficult to have to make changes, but once you get used to it, I’m sure you will find it much nicer to work with. It has various enhancements over mysqli which allow you to do more, but it is actually simpler to use.
for example in your code:-

@greenreader9

$stmt = $connect->prepare($sqlQuery);
$stmt->bind_param('s', $demovariable);
$sql = $stmt->execute()

In mysqli you have to go through the process of binding parameters like this. In PDO there is no need.

$stmt = $connect->prepare($sqlQuery);
$sql = $stmt->execute([$demovariable]);

You can simply pass the parameter value(s) directly into the execute method as an array.
That is just one example of how PDO can simplify your PHP.

[mabismad]

@greenreader9

apparently that is not good enough with SQL injection

Code is only as secure as you make it. A prepared query using mysqli is as secure as any other prepared query. The problems with using a mysqli prepared query are -

1. The mysqli prepared query programming interface is completely different from a non-prepared query, so, you must essentially learn two different sets of statements. If you are going to do this much work learning a database extension, you might as well just learn the much simpler and better designed PDO extension.
2. The mysqli prepared query programming interface is overly complicated, requiring more statements to accomplish a task, and at least two of the statements designed to make mysqli usable and more like the much better designed PDO extension, are dependent on php being built and using the mysqlnd driver, which you cannot guarantee unless you manage your own server. This results in non-portable code that wastes your time when you try to run it on a server that isn’t built to use the mysqlnd driver. In programming help thread after thread where this has occurred, the simplest solution is to convert the code to use the PDO extension.

An added bonus of using the PDO extension is that the statements can be used with 12 different database types, though there may be some differences in sql query syntax, so you don’t need to keep learning a completely new set of statements for each new database type you use.

Re, the error you are getting, regardless of the mysqli or PDO extension, the returned value from the ->execute() call is a boolean true/false value, if the execute statement succeeded or failed with an error. It is not used to call a fetch statement. Wherever you got that from wasn’t from the php.net documentation.

When you convert your code to use the PDO extension, name the connection variable $pdo or similar so that you can easily distinguish and search for code that has and has not been converted.

Other issues in the code you have posted -

1. Don’t use a php short opening <? tag. Again, this results in non-portable code that won’t work on some servers and you may not be able to change the setting necessary to make it work.
2. For the specific SELECT query you are showing, this should not be part of post method form processing. For searching for data, you should use a get method from. If this query is part of deciding if you are going to insert or update data, simply us an INSERT … ON DUPLICTE KEY UPDATE … query.
3. Don’t use output buffering to make bad code work. This only hides non-fatal php errors and debugging output from your code when you perform redirects, hindering debugging. Only buffer output when you want to buffer output.
4. Don’t conditionally start sessions. Your code should be organized with an initialization section so that any page that needs to use a session can simply preform a session_start.
5. At the point of testing if(!isset($connect)){, in your connection code, unless you are requiring that code repeatedly, there won’t ever be a connection, so that logic is pointless.
6. Don’t bother trying to handle a connection error in your application code. The visitor to your site doesn’t need to know and doesn’t care why a web page isn’t working and giving a hacker feedback if they were successful in causing an error will just encourage them to do more.

[greenreader9]

I cannot figure this out for the life of me

<?php
ob_start();
if(session_status() == PHP_SESSION_NONE){
	session_start();
}


$DataBase = array(
	"hostname" => "sql.hostinname.com",
	"username" => "username",
	"password" => "passsword",
	"name" => "table_name"
);


if(!isset($connect)){
	$servername = $DataBase['hostname'];
	$dbnane = $DataBase['name'];
	$username = $DataBase['username'];
	$password = $DataBase['password'];

	try {
	  $conn = new PDO("mysql:host=$servername;dbname=$dbnane", $username, $password);
	  // set the PDO error mode to exception
	  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	  echo "Connected successfully";
	} catch(PDOException $e) {
	  echo "Connection failed: " . $e->getMessage();
	}
}



$sqlQuery = "SELECT * FROM `area` WHERE `area_key`='AREA'";
$stmt = $connect->prepare($sqlQuery);           //Line 35
$sql = $stmt->execute();

$AreaInfo = $sql->fetch_assoc();         //When testing before, this gave the error, now the error seems to be ealier



echo $AreaInfo['area_status'];

?>

DB:

And output:

Connected successfully
Fatal error : Uncaught Error: Call to a member function prepare() on null in /path/to/file/test.php:35 Stack trace: #0 {main} thrown in /path/to/file/test.php on line 35

[benanamen]

Your connection is $conn but you are using $connection

And get rid of the isset, session status check and the object start like you were told.

And you’re still not using a prepared statement. Try reading the tutorial I gave you.

And database name variable is not a table name. Is the name of the database you are using. You also went from spelling name, to nane

You are also inconsistent with your variable naming case convention and you are creating extra variables for nothing.

[greenreader9]

@benanamen

And you’re still not using a prepared statement. Try reading the tutorial I gave you.

Well I actually wanted to get it working before I made it secure. No use making something secure if it does not even work in the first place.

@benanamen

you are creating extra variables for nothing.

The array is part of a different part of the program, and is called in via require. I just moved it in to this test file to make things more clean. And If I replace $connect = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); with $connect = new PDO("mysql:host=$DataBase['hostname'];dbname=$DataBase['name']", $DataBase['username'], $DataBase['hostname']password);, I get some sort of whitespace error.

So here is the updated file:

<?php
session_start();


$DataBase = array(
	"hostname" => "sql.hostinname.com",
	"username" => "username",
	"password" => "passsword",
	"name" => "db_name"
);


if(!isset($connect)){
	$servername = $DataBase['hostname'];
	$dbname = $DataBase['name'];
	$username = $DataBase['username'];
	$password = $DataBase['password'];

	try {
	  $connect = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
	  // set the PDO error mode to exception
	  $connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	  echo "Connected successfully";
	} catch(PDOException $e) {
	  echo "Connection failed: " . $e->getMessage();
	}
}



$sqlQuery = "SELECT * FROM `hosting_area` WHERE `area_key`='AREA'";
$stmt = $connect->prepare($sqlQuery);
$sql = $stmt->execute();

$AreaInfo = $sql->fetch_assoc();    //Line 35



echo $AreaInfo['area_status'];

?>

And now it goes back to the error I was getting before:

Connected successfully
Fatal error : Uncaught Error: Call to a member function fetch_assoc() on bool in /path/to/file/test.php:35 Stack trace: #0 {main} thrown in /path/to/file/test.php on line 35

[benanamen]

Why do you think this is how it’s done? Seems to me you still have not read the tutorial I gave you.

@greenreader9

$AreaInfo = $sql->fetch_assoc();

Lets just save some time.

$sqlQuery = "SELECT * FROM `area` WHERE `area_key`= ?";
$stmt = $connect->prepare($sqlQuery);
$AreaInfo = $stmt->execute(['AREA']);
$AreaInfo = $stmt->fetch(PDO::FETCH_ASSOC);
echo $AreaInfo['area_status'];

[greenreader9]

Mucho gracias.

Now I will read that article knowing that this is not a waste of time.

[SamA74]

@benanamen

$AreaInfo = $stmt->fetch(PDO::FETCH_ASSOC);

Also note that if you are mostly using FETCH_ASSOC (or any of the other many fetch modes PDO has to offer), you can set a default mode in the options on connection.
Then you may just use $stmt->fetch() to use your chosen default (most common) mode.
If you need to use another mode, you can still put it in the fetch.