I am working on a project with PHP and I perform these types of queries:
$stmt = $conn->prepare($sql);
// Bindeo algunos parametros...
$stmt.execute();
I want to know what is the most efficient way to manage any type of error that may occur when doing the query, be it the one I wrote, or a insert()
, a executeUpdate()
, etc. Since I don't know if they throw exceptions or if it's just enough to validate the return values.
What is the most complete way to manage these errors?
if($stmt.execute())
echo "Exito!";
else
echo "Falló";
Or handle exceptions:
try {
$stmt.execute();
} catch(Exception $e) {
print_r($e);
}
Combine both? Does it change anything if they are insert or update operations?
When using PDO, two very important aspects must be taken into account regarding the security of data handling. One of them has to do precisely with handling exceptions.
Yo
Before looking at those two aspects, I want to first say that the best way to use PDO is to have a class dedicated to handling our connection to PDO , which we will use every time we need to connect to the database. That saves us from having to create the PDO object each time , configure it properly, etc, etc. Having a class we simply get our object using the keyword
new
and that's it. In addition, we can provide this class with custom methods according to our needs, binding and other things could be simplified in an interesting way, making our code very easy.If you want, you can take a look at this Class I'm creating, and it now works perfectly for basic PDO use.
Let’s see then those two aspects mentioned above:
1. Exception handling and possible danger
When we create the connection to PDO, if the proper parameters are not passed when creating the connection object, PDO might reveal the connection credentials (username, password, database name...) in the
error_log
en case an error occurs . The error log, it goes without saying, is one of the favorite files of any hacker, precisely because interesting data can be found in it for any malicious user.How to avoid it?
By setting the following parameter on the connection:
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
. For more details see, in the PHP Manual: Errors and their handlingThis would then be one of the attributes that should be passed as an option to the PDO connection.
Let's see it here, this would be the connection method of a Class dedicated to managing our PDO connection. This is the method of the class mentioned above:
2. The problem of emulated preparations
The second important aspect has to do with prepared queries and emulated prepares.
The manual
PDO::ATTR_EMULATE_PREPARES
says the following:What is the problem?
That in PDO this option is set to
true
default, and if we don't set it tofalse
when creating the connection object, we could have an intelligently prepared SQL Injection by a malicious user.How this could happen is explained in full detail and with examples in my answer to the question: How to prevent SQL injection in PHP? .
So you'll see in the connection code above, we have this:
If this is not there, our connection object is simply not entirely secure and we could sooner or later be surprised by a SQL Injection even though we are using PDO.
II
Now let's move on to a second part that would answer your question more directly.
try ... catch
If we have a block like the one shown above in our connection class , we can forget about continuing to use it every time we have to query the database. The class will take care of that, which is why it exists. We will only have to deal with creating our connection object usingnew
, making the queries by calling the methods of the class and handling the data.Some basic uses, using our connection object
A
SELECT
no criteriaIt would be done simply like this:
And to check if there is data or not in the
SELECT
A
SELECT
with criteriaAnd to know if there is data, we check, as above, our variable
$datos
.an
UPDATE
anyoneTo verify if records were updated, we only have to evaluate the data variable, as we have done before, with the difference that in the case of
UPDATE
, if there are updates, the variable will contain all of them. Then:an
INSERT
anyoneTo check if records were inserted, we just have to evaluate the data variable, as we have done before in the case of
UPDATE
. Then:conclusion
As can be seen, the use of a dedicated class simplifies any further handling of the data. And the class you want to customize if you want to do other checks, depending on the needs of the programmer.
I think you are taking two different types of errors, and as such they should be treated differently.
One thing is a possible connection error, which can occur for multiple reasons, and quite another is that one
select
is incorrect or that the statement is not correctly written.In these cases, what I recommend is that you
try {} catch()
always use it when you create the connection and only in the queries in which it is necessary to perform some other action in case of failure, such as:On the contrary, if what you want is to have all the possible exceptions controlled and show them to the users without the horrible trappings of an exception, you can use the following:
http://www.php.net/set-exception-handler