PDO test if connected

Question

I can't find an answer to this anywhere. Maybe its really simple

I have my mysql PDO connection like this:

try{
$DBH = new PDO("mysql:host=$db_hostname;dbname=$db_database", $db_username, $db_password);
$DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );  
}
catch (PDOException $e){
echo $e->getMessage();
exit;
}

i want to just test if the connection worked, ie. if the password, username, databasename & hostname were spelled correctly.

the try, throw just seems to pick up fundamental errors, like if the driver is spelt wrong. it doesnt throw an error if say the password is wrong.

thanks

score 13 · Accepted Answer · edited May 23 '17 at 12:33

13

In a single click from this question, in the PDO tag wiki lies the exact how-to:

$dsn = "mysql:host=localhost;dbname=test;charset=utf8";
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$pdo = new PDO($dsn,'root','', $opt);

As well as a warning

DO NOT use try..catch operator just to handle an error message.
Uncaught exception already excellent for this purpose, as it will treat PDO errors just the same way as other PHP errors - so, you can define the behavior using site-wide settings. A custom exception handler could be added later, but not required. Especially for new users, it is recommended to use unhandled exceptions, as they are extremely informative, helpful and secure. More info...

edited May 23 '17 at 12:33

Community

1
1

answered Aug 14 '13 at 15:41

Your Common Sense

156,878
40
214
345

ok, so whats the right way just to test it? to test password etc is correct – Lan Aug 14 '13 at 15:53
You don't need to test. That's useless. You only need an error handler which will send a 500 header, log the error and kill the script. – Your Common Sense Aug 14 '13 at 15:57
but am i right in saying that it will only give an error when i try to execute a query? it wont give me a connection error when i just initiate a new PDO connection? – Lan Aug 14 '13 at 17:02
It will. just try it. The point is in setting exception mode *before* actual connect, not after, as in your code. – Your Common Sense Aug 14 '13 at 17:12
i've tried it. you can't set the PDO error mode before you initiate the new PDO connection. you get a Undefined variable ($DBH in my example). even in your anwser above, you're setting the error mode in/after your new intiation. just populating the array with the error mode parameters doesnt count as actually setting the error mode before the connection – Lan Aug 14 '13 at 21:28
so the point is, as far as i can tell, with PDO the only way to test if your password, hostname etc are correct is by trying to execute a query. unlike in standard mysql where you can test if mysql_connect gives a true boolean – Lan Aug 14 '13 at 21:32
ahhh. so sorry. just realised whats going on. the login file i was using to test what happens when password and other variables are wrong was the culprit. the password and username were set to empty which is what i have on my testing environment anyway so it was connecting!! - so sorry. wasted a couple of hours there – Lan Aug 14 '13 at 22:03
*"DO NOT use try..catch"* is good for this question using MySQL because you'll get a helpful error message on screen like `Access denied for user ... (using password: YES)`, but be careful when switching DBMS platforms because others, like MS SQL Server will actually spill the raw username and password in plain text. – Jeff Puckett Jul 07 '16 at 14:16
1

@JeffPuckettII you are confusing several things here. First, it is not MS SQL spills the password, but PHP. And so it does for any DBMS. To prevent PHP from echoing errors on-screen, you should switch displaying errors off. – Your Common Sense Jul 07 '16 at 14:19
That's strange because it doesn't spill the password with MySQL, but it does with MS SQL Server. I thought it was the error message generated by the DBMS, which PHP is merely passing through because these are the same error messages I get when using the console client without PHP. – Jeff Puckett Jul 07 '16 at 14:24
1

@JeffPuckettII nobody's intentionally reveals a password, neither mysql nor ms sql. if you see at the error message you will notice that it's just a regular PHP error message contains a stack trace. And stack traces expected to display a call stack... including function parameters. And a password among them. No magic here. To prevent PHP from revealing sensitive information with error messages, you have to configure it properly. – Your Common Sense Jul 07 '16 at 14:27
Yes! Thanks for the clarification. I just tested it and I actually can get it to spill the password with MySQL also. I appreciate you taking the time to explain that. – Jeff Puckett Jul 07 '16 at 14:35
@JeffPuckettII May I ask you to review [my argument with PHP manual on the matter](https://phpdelusions.net/pdo#errors) and tell me which side you on? You see, I find manual error handling with try catch rather harmful, while it's addressing wrong problem at all: instead of hiding one particular error message one should hide them all at once. – Your Common Sense Jul 07 '16 at 15:23
Good read - I'm sharing it with my team. I almost never use exceptions because I was taught in school to only use them to catch a recoverable error, and I tend to just code in advance conditional checks for those cases. I guess I haven't made an app that had an *optional* database connection, so yeah I invite the fatal error if there's a connection problem because it's not recoverable. – Jeff Puckett Jul 07 '16 at 15:46
1

@JeffPuckettII they taught you the right thing - exactly what I am saying in the article. But almost every other online source tells you to catch a connection error just to prevent it from displaying. While, in my opinion, they're barking the wrong tree, as displaying errors is a different matter and to prevent it one have to deal with relevant ini setting instead of catching an exception. So that's why I am a little too sensitive about that matter :) – Your Common Sense Jul 07 '16 at 15:52

score 3 · Answer 2 · edited Apr 04 '18 at 16:35

3

I use the following code to connect:

    <?php
class dbConnection extends PDO{

public function __construct() {    
  switch(DB_TYPE){
    case "mysql":
        $dbconn = "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET;
        break;
    case "sqlite":
        $dbconn = "sqlite:".DB_PATH.";charset=".DB_CHARSET;
        break;
    case "postgresql":
        $dbconn = "pgsql:host=".DB_HOST." dbname=".DB_NAME.";charset=".DB_CHARSET;
        break;
    }

    parent::__construct($dbconn,DB_USER,DB_PASS,array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

   }
}
?>

If I give wrong password, I get

Connection error, because: SQLSTATE[28000] [1045] Access denied for user 'microaid_logger'@'localhost' (using password: YES)

As Your common sense pointed out, an exception is already thrown in case the connection is not succesful, which will also trigger if the password is wrong. If you want to format the text of the error message or handle it, just set a custom error handler as described here

edited Apr 04 '18 at 16:35

James Jones

3,850
5
25
44

answered Aug 14 '13 at 15:36

coderkane

488
4
14

3

For catching connection error. It is very bad practice to use try..catch to handle the error message, makes code overbloated. Make your `errorHandler` function a *real* exception handler to act exactly the same way, but **without** the need of writing try..cach for the **every** query and you'll be okay. – Your Common Sense Aug 14 '13 at 15:49
Thanks for pointing that out, I corrected the answer - and learnt something new in the process :) – coderkane Aug 14 '13 at 16:03
Look, even without catch you will have your exception handled perfectly: it will be either logged or shown on-screen following ini. settings. And even have a backtrace with it. And PHP will send 500 status automatically as well – Your Common Sense Aug 14 '13 at 16:05
Yes, you are right; I added a note to add the custom error handler in case you want to send a http response code, or maybe redirect the user to a generic "oops" page after logging the error. – coderkane Aug 14 '13 at 16:20

PDO test if connected

2 Answers2

Linked