111

I was suggested, not long ago, to change my code to use PDO in order to parameterize my queries and safely save HTML in the database.

Well, here are the main problems:

  1. I looked at http://php.net/manual/en/ref.pdo-mysql.php, and I don't really get where I should put that $ ./configure --with-pdo-mysql string...

  2. The site I'm building actually only requires PDO for one page. While I may consider re-writing it, it would take a while and I need the pages to be running soon, so I can't turn off MySQL completely. If I do install PDO, will I still be able to use mysql_* handlers?

The server in question is running PHP Version 5.4.6-1ubuntu1 and Apache/2.2.22 (Ubuntu). I'm also running a phpMyAdmin database, if it matters.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Yuri Scarbaci
  • 1,475
  • 2
  • 11
  • 13

6 Answers6

196

On Ubuntu you should be able to install the necessary PDO parts from apt using sudo apt-get install php5-mysql

There is no limitation between using PDO and mysql_ simultaneously. You will however need to create two connections to your DB, one with mysql_ and one using PDO.

Jani Hartikainen
  • 42,745
  • 10
  • 68
  • 86
  • 1
    okay, i looked up in my php.ini file and it looks like pdo in enabled by default, i still need to know if i can use both pdo and mysql_*handlers... – Yuri Scarbaci Nov 14 '12 at 08:18
  • 1
    @YuriCollector'sEditionRossi Updated the answer for that. – Jani Hartikainen Nov 14 '12 at 08:21
  • 1
    It's worth mentioning that you need to restart apache afterwards for the changes to apply. – Adam Fowler Nov 23 '15 at 23:15
  • I'm pretty new to Linux, and when i run "sudo apt-get install php5-mysql", I get an error message saying that there is no installation candidate for this package. What am I doing wrong? – johnnydoe82 May 05 '16 at 16:54
  • 1
    @jonaspas if you are on ubuntu, try updating with `sudo apt-get update` then run the original install from the answer. – ryAn_Hdot Jun 08 '16 at 16:32
  • 17
    Thanks ryAn_Hdot! But that also didn't work for me. My solution now: I was running php7 and i had to do `sudo apt-get install php-mysql` – johnnydoe82 Jun 22 '16 at 16:45
  • sudo apt-get install php7.2-mysql worked for me on Ubunutu 20.04 LTS. Thanks – Sohel Pathan Jul 29 '23 at 23:00
25

That's a good question, but I think you just misunderstand what you read.

Install PDO

The ./config --with-pdo-mysql is something you have to put on only if you compile your own PHP code. If you install it with package managers, you just have to use the command line given by Jany Hartikainen: sudo apt-get install php5-mysql and also sudo apt-get install pdo-mysql

Compatibility with mysql_

Apart from the fact mysql_ is really discouraged, they are both independent. If you use PDO mysql_ is not implicated, and if you use mysql_ PDO is not required.

If you turn off PDO without changing any line in your code, you won't have a problem. But since you started to connect and write queries with PDO, you have to keep it and give up mysql_.

Several years ago the MySQL team published a script to migrate to MySQLi. I don't know if it can be customised, but it's official.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
artragis
  • 3,677
  • 1
  • 18
  • 30
  • so if i wanna use both at the same time i will have to use both the connection methods? what i mean is: i have like 50 pages already running, with a lot of code, it took like a year of developing and i can't turn back just for 1 page, not now anyway... what i need to do is have those 50 pages run as they are, and this one page i'm writing now using pdo, is it even possible? thanks for your time ^^ – Yuri Scarbaci Nov 14 '12 at 08:24
  • refactoring can be usefull if you have to change a lot of code. But yes, if you want yo use both, you have to establish two connections. – artragis Nov 14 '12 at 08:32
  • is there a easy why to refactore my code? i'm currently working on a work i didn't start my self, it was done by another programmer wich left it off, and now i have to adapt to his code... it's a bit harsh since everyone have his own style, so i would rather leave it as it is for now, since this software was used till now without any problem... thx btw ^^ do i need to close the mysql connection before opening the pdo one, or i can have both open at the same time? ^^ – Yuri Scarbaci Nov 14 '12 at 13:35
  • 1
    perhaps you can use an IDE like netbeans or eclipse – artragis Nov 14 '12 at 18:18
  • yep i'm already using it, it would have been impossible otherwise to continue with his code... btw i got it all working as i wanted it to, thanks a lot ^^ – Yuri Scarbaci Nov 15 '12 at 17:11
  • 1
    I found a script that allow you to migrate to mysqli, and perhaps to PDO – artragis Nov 20 '12 at 15:25
23

At first install necessary PDO parts by running the command

sudo apt-get install php*-mysql

where * is a version name of php like 5.6, 7.0, 7.1, 7.2

After installation you need to mention these two statements

extension=pdo.so
extension=pdo_mysql.so

in your .ini file (uncomment if it is already there) and restart server by command

sudo service apache2 restart
Avnish Tiwary
  • 2,188
  • 22
  • 27
  • 5
    This answer was extremely helpful because it pointed out you need the PHP version number in `php*-mysql`. I was trying `php-mysql` and `php7-mysql` to no avail and then this answer sealed the deal! Thanks! – jedmao Feb 07 '19 at 07:02
19

Basically the answer from Jani Hartikainen is right! I upvoted his answer. What was missing on my system (based on Ubuntu 15.04) was to enable PDO Extension in my php.ini

extension=pdo.so
extension=pdo_mysql.so

restart the webserver (e.g. with "sudo service apache2 restart") -> every fine :-)

To find where your current active php.ini file is located you can use phpinfo() or some other hints from here: https://www.ostraining.com/blog/coding/phpini-file/

Tobias Gaertner
  • 1,155
  • 12
  • 30
  • 1
    I didn't have these lines in my php.ini file. I only have `extension=pdo_mysql.dll`, so I uncommented that and restarted apache and it worked. – user3494047 Dec 07 '16 at 16:55
  • @user3494047 I guess you are on a windows system. I took the config excample from a linux system. But great that you shared your experience! – Tobias Gaertner Dec 12 '16 at 10:46
  • Actually its an ubuntu 14.04 – user3494047 Dec 12 '16 at 15:59
  • Thanks! I must have run a dozen `sudo apt-get install...` commands and nothing worked, finally adding these two lines to the php.ini did it. Just mentioning the apache server restart command that you run after saving : `sudo service apache2 restart` . And my php.ini file path was `/etc/php/7.0/apache2/php.ini` (OS: Lubuntu 16.04, analogous to Ubuntu for these purposes). Run `sudo gedit ` to edit it. (gedit or geany or any text editor on your system) – Nikhil VJ Sep 06 '17 at 02:54
  • Thanks for the details. Actually it can be very different where your php.ini is located. You can even overwrite it per project. I updated my answer. – Tobias Gaertner Sep 06 '17 at 08:32
9
  1. PDO stands for PHP Data Object.
  2. PDO_MYSQL is the driver that will implement the interface between the dataobject(database) and the user input (a layer under the user interface called "code behind") accessing your data object, the MySQL database.

The purpose of using this is to implement an additional layer of security between the user interface and the database. By using this layer, data can be normalized before being inserted into your data structure. (Capitals are Capitals, no leading or trailing spaces, all dates at properly formed.)

But there are a few nuances to this which you might not be aware of.

First of all, up until now, you've probably written all your queries in something similar to the URL, and you pass the parameters using the URL itself. Using the PDO, all of this is done under the user interface level. User interface hands off the ball to the PDO which carries it down field and plants it into the database for a 7-point TOUCHDOWN.. he gets seven points, because he got it there and did so much more securely than passing information through the URL.

You can also harden your site to SQL injection by using a data-layer. By using this intermediary layer that is the ONLY 'player' who talks to the database itself, I'm sure you can see how this could be much more secure. Interface to datalayer to database, datalayer to database to datalayer to interface.

And:

By implementing best practices while writing your code you will be much happier with the outcome.

Additional sources:

Re: MySQL Functions in the url php dot net/manual/en/ref dot pdo-mysql dot php

Re: three-tier architecture - adding security to your applications https://blog.42.nl/articles/introducing-a-security-layer-in-your-application-architecture/

Re: Object Oriented Design using UML If you really want to learn more about this, this is the best book on the market, Grady Booch was the father of UML http://dl.acm.org/citation.cfm?id=291167&CFID=241218549&CFTOKEN=82813028

Or check with bitmonkey. There's a group there I'm sure you could learn a lot with.

>

If we knew what the terminology really meant we wouldn't need to learn anything.

>

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Elaine Ossipov
  • 99
  • 1
  • 2
1

If you need a CakePHP Docker Container with MySQL, I have created a Docker image for that purpose! No need to worry about setting it up. It just works!

Here's how I installed in Ubuntu-based image:

https://github.com/marcellodesales/php-apache-mysql-4-cakephp-docker/blob/master/Dockerfile#L8

RUN docker-php-ext-install mysql mysqli pdo pdo_mysql

Building and running your application is just a 2 step process (considering you are in the current directory of the app):

$ docker build -t myCakePhpApp .
$ docker run -ti myCakePhpApp
Marcello DeSales
  • 21,361
  • 14
  • 77
  • 80
  • In case anyone tries to do this with an already running docker container: `sudo /etc/init.d/apache2 restart` is needed to restart the apache server – MajorBreakfast Jan 03 '18 at 18:16