7
\$\begingroup\$

I have been curious about how ORMs really work under the hood so I decided to do something simple in PHP.

So I basically built two Interfaces: DatabaseInterface and the ORMMapper interface and two Classes: MysqlAdapter and the ORMMapper Base Class then a test model that extends the ORMMapper as shown in the usage below.

DatabaseInterface.php:

interface DatabaseInterface{

/**
 * @return bool
 */
function connect();

/**
 * @return void
 */
function disconnect();

/**
 * @param string $tableName
 * @param array $columns
 * @param array $values
 * @return mixed
 */
function insert($tableName, $columns, $values);

/**
 * @param string $tableName
 * @param array $conditions
 * @param array $columns
 * @param array $values
 * @return mixed
 */
function update($tableName, $columns, $values, $conditions);

/**
 * @param string $tableName
 * @param string $columns
 * @param array $conditions
 * @param int $limit
 * @param int $offset
 * @return mixed
 */
function select($tableName, $columns,  $conditions, $limit, $offset);

/**
 * @param string $tableName
 * @param array $conditions
 * @return mixed
 */
function delete($tableName, $conditions);

/**
 * @param string $tableName
 * @return array
 */
function fetchFields($tableName);
}

MapperInterface.php:

interface MapperInterface{

function findById($id);

function save();

function loadClassProperties();
}

MysqlAdapter.php:

class MysqlAdapter implements DatabaseInterface
{
private $host = '';

private $username = '';

private $password = '';

private $dbName = '';

private $port = '';

private $socket = '';

private $_mysqli;

function __construct($host, $username, $password, $dbName, $port = null, $socket = null)
{
    $this->host = $host;
    $this->username = $username;
    $this->password = $password;
    $this->dbName = $dbName;
    $this->port = $port;
    $this->socket = $socket;
}

function connect()
{
    $this->_mysqli = new mysqli($this->host, $this->username, $this->password, $this->dbName, $this->socket);
    if ($this->_mysqli->connect_error) {
        return false;
    }
    return true;
}

function disconnect()
{
    if (isset($this->_mysqli)) {
        $this->_mysqli->close();
    }
}

/**
 * @param string $tableName
 * @param array $columns
 * @param array $values
 * @return mixed
 */
function insert($tableName, $columns, $values)
{
    $query = "INSERT INTO $tableName $columns VALUES $values";
    return $this->_mysqli->query($query);
}

/**
 * @param string $tableName
 * @param array $conditions structure -> column => (operator, value, logical_operator) e.g id => (>, 5, AND)
 * @param array $columns
 * @param array $values
 * @return mixed
 */
function update($tableName, $columns, $values, $conditions)
{
    $updateString = $this->generateUpdateString($columns, $values);
    $whereString = $this->generateWhereString($conditions);
    $query = "UPDATE $tableName SET  $updateString WHERE  $whereString";
    $result = $this->_mysqli->query($query);
    return $result;
}

/**
 * @param string $tableName
 * @param string $columns
 * @param array $conditions
 * @param int $limit
 * @param int $offset
 * @return mixed
 */
function select($tableName, $columns, $conditions, $limit = null, $offset = null)
{
    $query = "SELECT $columns FROM $tableName";
    if (!empty($conditions)) {
        $whereString = $this->generateWhereString($conditions);
        $query .= " WHERE $whereString";
    }
    if (isset($limit) && isset($offset)) {
        $query .= "LIMIT $limit OFFSET $offset";
    }
    $result = $this->_mysqli->query($query);
    $response = [];
    if($result){
        $response['fields'] = $this->fetchFields($result);
        $response['values'] = mysqli_fetch_all($result);
    }
    return $response;
}

/**
 * @param string $tableName
 * @param array $conditions
 * @return mixed
 */
function delete($tableName, $conditions)
{
    $whereString = $this->generateWhereString($conditions);
    $query = "DELETE FROM $tableName WHERE $whereString";
    return $query;
}

/**
 * @param array $keys
 * @param array $values
 * @return string
 */
function generateUpdateString($keys, $values)
{
    $len = count($keys);
    $buildString = '';
    for ($i = 0; $i < $len - 1; $i++) {
        $buildString .= $keys[$i] . '=' . $values[$i] . ',';
    }
    $buildString .= $keys[$len - 1] . '=' . $values[$len - 1];
    return $buildString;
}

/**
 * @param array $arrayValues
 * @return string
 */
public function generateWhereString($arrayValues)
{
    $buildString = '';
    foreach ($arrayValues as $key => $arrayValue) {
        $buildString .= $key . $arrayValue[0] . $arrayValue[1] . " " . $arrayValue[2];
    }
    return $buildString;
}

/**
 * @param string $queryResult
 * @return array
 */
function fetchFields($queryResult)
{
    if ($queryResult) {
        $fieldsData = $queryResult->fetch_fields();
        $fields = [];
        foreach ($fieldsData as $fieldData) {
            $fields[] = $fieldData->name;
        }
        return $fields;
    }
    return [];
}
}

ORMMapper.php

class ORMMapper implements MapperInterface
{

private $_tableName = '';

private $_adapter;

function __construct()
{
    $this->_adapter = new MysqlAdapter('dbhost', 'username', 'password', 'test');
    if (!$this->_adapter->connect()) {
        echo "Something Terribly went wrong";
        return;
    }
    $this->loadClassProperties();
}

/**
 * @return object
 */
function findAll()
{
    $result = $this->_adapter->select($this->_tableName, '*', []);
    return $this->buildResponseObject($result);
}

/**
 * @param $id
 * @return object
 */
function findById($id)
{
    $result = $this->_adapter->select($this->_tableName, '*', ['id' => ['=', $id, '']]);
    $result = $this->buildResponseObject($result);
    if ($result){
        return $result[0];
    }
    return (object)[];
}


/**
 * @return mixed
 */
function save()
{
    // todo: Complete the Implementation of this method
    $fields = $this->_adapter->fetchFields($this->_tableName);
    if (isset($this->id)) {
        return $this->_adapter->update($this->_tableName, $fields, (array)$this, ['id' => ['=', $this->id, '']]);
    }
    return $this->_adapter->insert($this->_tableName, $fields, (array)$this);
}

function loadClassProperties()
{
    $fields = $this->_adapter->fetchFields($this->_tableName);
    foreach ($fields as $field) {
        $this->$field = null;
    }
}

/**
 * @param $result
 * @return object
 */
function buildResponseObject($result)
{
    $response = [];
    if ($result) {
        $fields = $result['fields'];
        $values = $result['values'];
        $num_of_rows = count($result['values']);
        $num_of_fields = count($result['values'][0]);

        $buildResponse = [];
        for ($i = 0; $i < $num_of_rows; $i++) {
            for ($j = 0; $j < $num_of_fields; $j++) {
                $buildResponse[$fields[$j]] = $values[$i][$j];
            }
            $response[] = $buildResponse;
        }
    }
    return json_decode(json_encode($response));
}


/**
 * @param $tableName
 */
public function setTableName($tableName)
{
    $this->_tableName = $tableName;
}
}

TestModel.php

class TestModel extends ORMMapper{
private $tableName = 'users';
function __construct()
{
    parent::__construct();
    parent::setTableName($this->tableName);
}
}

Usage: Index.php

$users = new TestModel();
$users = $users->findAll();

Result -Note: Test Table users has 3 columns (id, i, second) with 3 rows of random data

 var_dump($users);

 array(3) { [0]=> object(stdClass)#4 (3) { ["id"]=> string(1) "1" ["i"]=> NULL ["second"]=> NULL } [1]=> object(stdClass)#7 (3) { ["id"]=> string(1) "2" ["i"]=> NULL ["second"]=> NULL } [2]=> object(stdClass)#6 (3) { ["id"]=> string(1) "3" ["i"]=> string(1) "1" ["second"]=> string(1) "2" } }

This gives objects with data which can then be used in the application. Did I do anything wrong? and how could I have done it better in my implementation?

\$\endgroup\$
1
  • 1
    \$\begingroup\$ Please use PDO instead of mysqli for any new projects. \$\endgroup\$
    – Dharman
    Commented Mar 7, 2023 at 14:07

2 Answers 2

6
\$\begingroup\$

One thing I believe is obvious is that your ORM is subject to SQL injection unless I am missing something.

For example, in my understanding, you could make something like this

$users = new TestModel;
$users = $users->findById("0;DELETE FROM users");

In your method, you send to the adapter's select method the string 0;DELETE FROM users. When you parse the string, the generateWhereString will return:

id=0;DELETE FROM users

And all your SQL sentences will become:

SELECT * FROM users WHERE id=0;DELETE FROM users

Maybe I am missing something because I have not tried it, but that was the first thing that popped into my head when I saw your code. If I am wrong, I will gladly take that back.

\$\endgroup\$
2
  • 4
    \$\begingroup\$ Can you point out where in the code it's vulnerable? \$\endgroup\$ Commented Jul 20, 2017 at 18:16
  • 4
    \$\begingroup\$ Welcome to CR! That's a very excellent thing to point out - with a bit more fleshing it could become a great review! As it stands, it's more of a comment. Can you edit to expand some more? \$\endgroup\$ Commented Jul 20, 2017 at 18:20
3
\$\begingroup\$

I am particularly interested in how ORMs really work, but not succeeded with writing an exemplary mapper yet. So I am really interested in your approach, which is quite good to my taste, save for several issues:

  1. First of all, as it was pointed out in the other answer, there is no protection from SQL injection whatsoever.
  2. Also a severe issue: your code will create as many connections to DB server as many mappers will be created. It will kill your server with the notorious "Too many connections" error.
  3. Objects returned are instances of stdClass, which is not very convenient. There should be a class name set in the mapper and it should return an instance of this class.
  4. There are some functions that are of no use. findAll() for example. I hardly can imagine a case when you need to get all users, even without LIMIT or ORDER BY. The same goes for the select() method. The only simple use case (select('tablename')) is of no use - in reality you'll never call it this way. Whereas any other use is insecure and gibberish. Why don't you want to let just arbitrary query? It will be more flexible, more secure and more readable than current select() method.

In the end, I would suggest

  1. Change mysqli to PDO for mysql adapter as it will make the code more usable with prepared statements and much easier for creating other adapters.
  2. Create a factory pattern for providing a single database connection for your classes. You can see an example here
  3. Change select() method to query($sql, $params) for letting an arbitrary query with parameters. You can use then this query() method in all other methods, from insert() to delere().
  4. Remove fetchAll() method and add something like findByFilters(). And this function should be probably moved into adapter.
  5. Re-write your insert() and update() methods to make sure that field names are strictly going from the class properties and values are added via parameters.
\$\endgroup\$
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.