Working with PHP and MySQL - need a good and secure OO design

Question

I am new to PHP-> first time developer. I am working on my web application and it is nearly done; nevertheless, most of my sql was done directly via code using direct mysql requests. This is the way I approached it:

In classes_db.php I declared the db settings and created methods that I use to open and close DB connections. I declare those objects on my regular pages:

class classes_db {

    public $dbserver = 'server;
    public $dbusername = 'user';
    public $dbpassword = 'pass';
    public $dbname = 'db';

    function openDb() {
        $dbhandle = mysql_connect($this->dbserver, $this->dbusername, $this->dbpassword);
        if (!$dbhandle) {
            die('Could not connect: ' . mysql_error());
        }
        $selected = mysql_select_db($this->dbname, $dbhandle)
                or die("Could not select the database");

        return $dbhandle;
    }


    function closeDb($con) {
        mysql_close($con);
    }
}

On my regular page, I do this:

<?php
require 'classes_db.php';
session_start();

//create instance of the DB class
$db = new classes_db();

//get dbhandle
$dbhandle = $db->openDb();

//process query
$result = mysql_query("update user set username = '" . $usernameFromForm . "' where iduser= " . $_SESSION['user']->iduser);

//close the connection
if (isset($dbhandle)) {
    $db->closeDb($dbhandle);
}

?>

My questions is: how to do it right and make it OO and secure? I know that I need incorporate prepared queries-> how to do it the best way? Please provide some code

Stop using `mysql_*` and use PDO with prepared queries. As it stands right now, you are likely wide open to SQL injection attacks. — Brad, Nov 03 '12 at 16:51
I know that, this is the purpose of my question-> please provide some best way to improve it. Thank you — Andrew, Nov 03 '12 at 16:52
See http://bobby-tables.com/php.html for examples of how to properly use parametrized SQL queries. — Andy Lester, Nov 03 '12 at 16:58
@Andrew, I did suggest a way to fix it... Use PDO with prepared queries. — Brad, Nov 03 '12 at 17:00
OP, see my answer. It will help keep things secure, and DRY http://en.wikipedia.org/wiki/Don't_repeat_yourself — Matthew Blancarte, Nov 03 '12 at 17:02

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

stop using mysql_* , use mysqli and PDO instead

PHP PDO vs normal mysql_connect

mysql_* functions are getting old. For a long time now mysql_* has been at odds with other common SQL database programming interfaces. It doesn't support modern SQL database concepts such as prepared statements, stored procs, transactions etc... It's method for escaping parameters with mysql_real_escape_string and concatenating into SQL strings is error prone and old fashioned. The other issue with mysql_* is that it has had a lack of attention lately from developers, it is not being maintained... Which could mean things like security vulnerabilities are not getting fixed, or it may stop working altogether with newer versions of MySQL. Also lately PHP community have seen fit to start a soft deprecation of mysql_* which means you will start seeing a slow process of eventually removing mysql_* functions altogether from the language (Don't worry this will probably be awhile before it actually happens!).

PDO has a much nicer interface, you will end up being more productive, and write safer and cleaner code. PDO also has different drivers for different SQL database vendors which will allow you to easily use other vendors without having to relearn a different interface. (though you will have to learn slightly different SQL probably). Instead of concatenating escaped strings into SQL, in PDO you bind parameters which is an easier and cleaner way of securing queries. Binding parameters also allow for a performance increase when calling the same SQL query many times with slightly different parameters. PDO also has multiple methods of error handling. The biggest issue I have seen with mysql_* code is that it lacks consistent handling, or no handling at all! With PDO in exception mode, you can get consistent error handling which will end up saving you loads of time tracking down issues.

PDO is enabled by default in PHP installations now, however you need two extensions to be able to use PDO: PDO, and a driver for the database you want to use like pdo_mysql. installing the MySQL driver is as simple as installing the php-mysql package in most distributions.

http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

Matthew Blancarte · Answer 2 · 2012-11-03T17:21:04.983

0

I highly recommend RedBeanPHP. It's a very solid MySQL database library that abstracts out all of the connection, querying, sanitization, preparation, uses PDO, etc. of your database interaction (which would be great for you, considering you are new to PHP development).

Check it out here: http://redbeanphp.com/

An example of how easy it is to set up a connection:

require('rb.php');
R::setup('mysql:host=localhost; dbname=mydatabase','user','password');

Setting up RedBeanPHP: http://redbeanphp.com/manual/setup

When creating a class to deal with your database objects, you'll want to use an interface and/or an abstract class to set certain object behavior rules (think CRUD...).

All you need to do is include that database connection in a common file, include it in all of your PHP scripts, and you're all set.

edited Nov 03 '12 at 17:21

answered Nov 03 '12 at 16:57

Matthew Blancarte

8,251
2
25
34

I disagree that using a library like this is good for new developers. They need know how PHP and MySQL work before trying libraries so they know how to fix problems with the library when they arise. – Codeguy007 Nov 03 '12 at 17:04
Totally disagree. Newcomers should start with tools that ensure proper patterns and practices, and discover the finer points of a language/stack further down the line. The last thing you want to do as a new developer is jump into the deep end without knowing how to swim. – Matthew Blancarte Nov 03 '12 at 17:07

Working with PHP and MySQL - need a good and secure OO design

2 Answers2