Thread

Hi everyone

I could use a little help in config. to get CI talking to my db using pdo. 
I also have a few questions about (the necessity, for injection-safety?) of using pdo in CI.

I have a CI project/app for which I initially coded it using Active Record, and I just upgraded to using CI 2.1.1 so that I could rewrite my model files to use (the now CI-supported) PDO,
as given the climate in security threads on PHP forums, etc, - the professional climate is pressuring to move away from escaping and into PDO/bound parameters etc.

Anyway, I modified ‘application/config/database.php’ like so:

[snip]

$active_group = 'local_dev';

$active_record = TRUE;//<---will this need to stay TRUE to make CI sessions work?  For better security, don't we want db-based CI sessions to use PDO too?
//http://ellislab.com/codeigniter/user-guide/database/configuration.html:
    //Note: that some CodeIgniter classes such as Sessions require Active Records be enabled to access certain functionality.

//this is the config setting that I am guessing is my main problem: (?)
$db['local_dev']['hostname'] = 'localhost:/tmp/mysql.sock';
// 1.) if $db['local_dev']['dbdriver']='mysql', then here ^^^ 'localhost:/tmp/mysql.sock' works, 2.) but if $db['local_dev']['dbdriver']='pdo', then it fails with error msg. shown below.

$db['local_dev']['username'] = 'root';
$db['local_dev']['password'] = '';
$db['local_dev']['database'] = 'mydbname';
$db['local_dev']['dbdriver'] = 'pdo';
$db['local_dev']['dbprefix'] = '';
$db['local_dev']['pconnect'] = TRUE;
$db['local_dev']['db_debug'] = TRUE;//TRUE
$db['local_dev']['cache_on'] = FALSE;
$db['local_dev']['cachedir'] = '';
$db['local_dev']['char_set'] = 'utf8';
$db['local_dev']['dbcollat'] = 'utf8_general_ci';
$db['local_dev']['swap_pre'] = '';
$db['local_dev']['autoinit'] = TRUE;
$db['local_dev']['stricton'] = FALSE;
[snip]

With the above config., as soon as I load a controller, I get this error message:

Fatal error: Uncaught exception ‘PDOException’ with message ‘could not find driver’ in
/Library/WebServer/Documents/system/database/drivers/pdo/pdo_driver.php:114 Stack trace: #0
/Library/WebServer/Documents/system/database/drivers/pdo/pdo_driver.php(114): PDO->__construct(‘localhost:/tmp/...’, ‘root’, ‘’, Array) #1 /Library/WebServer/Documents/system/database/DB_driver.php(115): CI_DB_pdo_driver->db_pconnect() #2
/Library/WebServer/Documents/system/database/DB.php(148): CI_DB_driver->initialize() #3
/Library/WebServer/Documents/system/core/Loader.php(346): DB(’‘, NULL) #4
/Library/WebServer/Documents/system/core/Loader.php(1171): CI_Loader->database() #5
/Library/WebServer/Documents/system/core/Loader.php(152): CI_Loader->_ci_autoloader() #6
/Library/WebServer/Documents/system/core/Con in
/Library/WebServer/Documents/system/database/drivers/pdo/pdo_driver.php on line 114

I tried swapping out the ‘pdo_driver.php’ file from the one on github, as per this:
http://ellislab.com/forums/viewthread/206124/
...but that just generates other errors, not to mention is disturbing to a newbie who does not want to touch the system files if at all possible.

This thread also seems to imply the need to be hacking the ‘pdo_driver.php’ system file:
http://stackoverflow.com/questions/11054618/codeigniter-pdo-database-driver-not-working
It seems odd to me that (someone thought that) a hack to a system file is needed to make PDO work in CI v.2.1.1, doesn’t it?

——————————————————————————————————————————————————————————

Another (ongoing) question I have is this:
Why are people complaining about CI’s implementation of PDO?  Like e.g.:
http://ellislab.com/forums/viewthread/218455/

“PDO with only support for QUERY is pretty useless”

Can we indeed use prepare/Execute, or not?  Do we really need to (for guaranteed safety against SQL injection)?  If not, then why all the relentless fuss (on PHP forums) about PDO/bound parameters anyway (in terms of security)?
(I have not used PDO before, nor anything other than CI’s Active Record, so forgive me if I am asking what may supposed to be obvious.
There seems to be a lot of confusion about what is best security practice.. and how to follow it, with CI.  Here’s some others that (for me anyway) just adds to the feeling of being unsure:
http://stackoverflow.com/questions/9765128/codeigniter-pdo-driver-uses-query-instead-of-prepare-isnt-this-less-secure
http://stackoverflow.com/questions/8743943/codeigniter-pdo-integration

http://stackoverflow.com/questions/9284274/codeigniter-using-pdo-instead-of-mysql

There are also MySQLi and PDO drivers. None of them use prepared statements because parameter-placeholder escaping is done by CI already. – Francis Avila”

...but in other threads I am not finding just now, people complain that in the end (even for CI’s “Query Bindings”) CI still relies on mysql_* to escape.. and that is inherently unsafe.
?

Thanks for any feedback, especially how to just get PDO working for my (first on localhost) CI app.

Hello!
Not sure I can help you, but I had some problems to get it to work aswell, and what I did was that he suggest in the other thread (which I asked stuff in myself).

I set hostname to this

$db['default']['hostname'] = 'mysql:localhost';

Why he think that its useless to use PDO without prepare/execute is because you gain no extra security, and how I do my queries are like this:

$sth = $this->db->conn_id->prepare("SELECT * FROM tbl_movies");
$sth->execute(array("Harry%Potter"));

(or you can use bindParam if you prefer that)

Using PDO, you dont have to escape your string when inserting, which is something I like about it.

Sweden,
thanks for trying to help here!

When I change that config. item to what worked for you, i.e.:

$db['local_dev']['hostname'] = 'mysql:localhost';

...then I get an even longer error:

A PHP Error was encountered

Severity: Warning

Message: PDO::__construct() [pdo.—construct]: [2002] No such file or directory (trying to connect via unix:///var/mysql/mysql.sock)

Filename: pdo/pdo_driver.php

Line Number: 114

Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[HY000] [2002] No such file or directory’ in
/Library/WebServer/Documents/system/database/drivers/pdo/pdo_driver.php:114 Stack trace: #0
/Library/WebServer/Documents/system/database/drivers/pdo/pdo_driver.php(114): PDO->__construct(‘mysql:localhost…’, ‘root’, ‘’, Array) #1
/Library/WebServer/Documents/system/database/DB_driver.php(115): CI_DB_pdo_driver->db_pconnect() #2
/Library/WebServer/Documents/system/database/DB.php(148): CI_DB_driver->initialize() #3
/Library/WebServer/Documents/system/core/Loader.php(346): DB(’‘, NULL) #4
/Library/WebServer/Documents/system/core/Loader.php(1171): CI_Loader->database() #5
/Library/WebServer/Documents/system/core/Loader.php(152): CI_Loader->_ci_autoloader() #6
/Library/WebServer/Documents/ in
/Library/WebServer/Documents/system/database/drivers/pdo/pdo_driver.php on line 114

Notice before, when I had it set like so:

$db['default']['hostname'] = 'localhost:/tmp/mysql.sock';

...then at least there was not that first error about

No such file or directory

.

Did you ever have it working with the mysql driver instead of PDO?  In other words, did you ever have this config item set like so:

$db['default']['dbdriver'] = 'mysql';

?

If you did successfully try/use the mysql driver before, then what was your

$db['default']['hostname']

then?
When I use the mysql driver, then I have to set hostname like this:

$db['default']['hostname'] = 'localhost:/tmp/mysql.sock';

Your answer may give enough clue/insight to figure this out.

I feel silly, because I am sure the issue is simple for experts who actually run mysql servers for a living 😉
Or is the issue having to do with CI code?  If so, then how could CI 2.1.0 (or 2.1.1) even be released?

————————————————————————————————————————————————————————————————————————

On the topic of security, I would really like to get to the bottom of the whole discussion.  I have read so many threads where it is evident to me that very few really know the true situation.  I am just learning, but AFAICT; I *think*, the topic boils down to these points (in my own words):

1.) Code Igniter, all along, has been using mysql_real_escape_string() to protect against SQL injection.  Even CI’s “Query Bindings”, despite their appearance, and Active Record, essentially *rely on* mysql_real_escape_string().  Someone please correct me if I am wrong.  I am new enough to OOP to be daunted to reverse engineer CI’s classes to prove definitive answers to myself, one way or the other.

2.) Many people feel that escaping is not really safe anymore.  E.g.:
http://marc.info/?l=php-general&m=131603743606025&w=2
...and they yell at everyone who still uses escaping.  But it is ironic because countless apps/libraries/professionals still *reliably/successfully* rely on escaping for protection, everyday.  If done right, escaping still works, despite the hype against it.  Someone please correct me if I am wrong (please demo a hack that gets around *properly use of* mysql_real_escape_string().

Now with the PDO driver we have the possibility to use PDO, but (and here I do not really know, but am just guessing by what I have seen other newbie’s write) - CI’s implementation of PDO does not yet allow use of prepare/Execute ... and so misses the chance to take advantage of the reason that PDO is so attractive to the experts who warn against relying on escaping, in the first place.

But what you wrote show you using prepare/Execute, right?  So then why do others say it is not possible yet?  And why did you say/confirm that,

...its useless to use PDO without prepare/execute ... because you gain no extra security

??  ...when you ARE using prepare/execute?

Also, is your example:

$sth = $this->db->conn_id->prepare("SELECT * FROM tbl_movies");
$sth->execute(array("Harry%Potter"));

...really complete?  Aren’t you missing the placeholder? ..where “Harry%Potter” gets inserted into the statement?

Thanks for any feedback

You’re right about my example, I was too quick to post that

$sth = $this->db->conn_id->prepare("SELECT * FROM tbl_movies WHERE fldMovieName LIKE ?");
$sth->execute(array("%Harry%Potter%"));

I think I cant however help you with the issue you got for connection to the database since I’m using Windows.
I didnt try to connect just using MySQL, I set it to PDO from the beginning, because thats what I’ve been using latley.

What I like most about PDO is that you dont get escaped string in the db, you have the raw data, and instead you use something like htmlspecialchars when printing it on the page.

does anyone have any idea how to solve this?  It is frustrating to not be able to use the PDO driver just because of a lone config setting or 2 that I am not getting right!

thanks to the noob thread http://ellislab.com/forums/viewthread/180277/ (InsiteFX’s answer)..

I figured out the below seems to work (need to test more.. but at least the error messages are gone:

$db['local_dev']['hostname'] = 'mysql:host=127.0.0.1';

as usual, i always configured my db con with DB PORT, by adding :

$db[‘local_dev’][‘port’] = ‘1433’; —> i used mssql 2008 DB

hope its help

oo one more i miss it, just used basic configuration:

$db[‘local_dev’][‘host’] = ‘localhost’;
$db[‘local_dev’][‘port’] = ‘1433’;
$db[‘local_dev’][‘database’] = ‘mydbname’;

‘1433’;  —> i used mssql 2008 DB the default port, i dont no the default with mysql maybe 3304 cek your mysql default port