Showing SQL errors with PDO

[lurtnowski]

Im trying to show the type of error if my query fails, but is this correct?

	try {
		 $sql = "INSERT INTO Customers (
		CustomerFirstName,customerMiddleName,customerLastName,customerAddress,customerCity,customerState,customerZip,customerAreaCode,customerPhoneNo,customerDateOpened,customerDateModified,accountID
		) VALUES (
		'$First_Name','$Last_Name','$Middle_Name','$Address','$City','$State',$Zip,$Area_Code,$Phone,NOW(),NULL,NULL)";
		
		
		$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    	$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

		
		$stmt = $dbh->prepare($sql);

  		$stmt->execute();
		
		$count = $stmt->rowCount();

	 if($count == 1) {
		
		echo '<h2 class="bg-success" style="padding:4px"><span class="glyphicon glyphicon-ok" aria-hidden="true"></span>&nbsp;&nbsp;Customer created</span></h2>';;
		
	 }	 
	} catch (PDOException $e) {
		echo $sql;
    	echo 'Exception -> '.var_dump($e->getMessage());
		echo '<h2 class="bg-danger" style="padding:4px"><span class="glyphicon glyphicon-remove" aria-hidden="true"></span>&nbsp;&nbsp;Customer NOT created</span></h2>';;
	}

Thanks

[SamA74]

Did you try it?
All you have to do is (temporarily) mess up your query so it will fail, then see what results.

[lurtnowski]

ok, I messed up the query up a little and get

INSERT INTO Cstomers ( CustomerFirstName,customerMiddleName,customerLastName,customerAddress,customerCity,customerState,customerZip,customerAreaCode,customerPhoneNo,customerDateOpened,customerDateModified,accountID ) VALUES ( ‘test first name’,‘test last name’,‘test m iddle nam’,‘test street address’,‘test city’,‘CA’,92118,619,9426938,NOW(),NULL,NULL)string(86) “SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘acp.Cstomers’ doesn’t exist” Exception →

I gather the

echo $sql;

is where the query came from, but where did the error come from?
and why did the Exception-> thing not work?

[colshrapnel]

There are two critical issues in your code.

And your idea on the error reporting is one of them.

Im trying to show the type of error if my query fails

To whom you want to show it in the first place? Remember that you are not the only user of your site! Do you really think that site users are that interested to know that Table ‘acp.Cstomers’ doesn’t exist? Or they are eager to read your SQL queries? Well, there are certainly some sort of visitors who is rather quite interested in this sort of information but they are last people you want to see it.

So I hope you understand now that the whole catch block is rather harmful and should be never used. Instead, just leave your exceptions alone and [almost] never catch them. PHP is intelligent enough to report the error to you automatically.

The second problem with this code is its essential vulnerability.
No offence, but this code looks exactly as a cargo cult programming. Certainly, you have heard somewhere that

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Makes your code safe from injection. But the problem is, such a straw prepared statement like yours will protect nothing at all. You should be using a real prepared statement instead.

So finally your code should be like

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$sql = "INSERT INTO Customers VALUES (NULL,?,?,?,?,?,?,?,?,?,NOW(),NULL,NULL)";
$stmt = $dbh->prepare($sql);
$stmt->execute([$First_Name,$Last_Name,$Middle_Name,$Address,$City,$State,$Zip,$Area_Code,$Phone]);
echo '<h2 class="bg-success" style="padding:4px"><span class="glyphicon glyphicon-ok" aria-hidden="true"></span>&nbsp;&nbsp;Customer created</span></h2>';;

And as of the trifle contusion in your last message, it’s simply from the misplaced var_dump() which is rather useless in that code but nonetheless ,as we learned already, the whole code should be removed at all.