MySqlConnection::escapeIdentifier() - Code Metrics - simply-framework/database - Measure and Improve Code Quality continuously with Scrutinizer

MySqlConnection::escapeIdentifier() A
last analyzed 2018-10-17 09:00 UTC

↳ Parent: MySqlConnection

Complexity

Conditions	1
Paths	1

Size

Total Lines	3
Code Lines	1

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	2
CRAP Score	1

Importance

Changes

Metric	Value
cc	1
eloc	1
nc	1
nop	1
dl	0
loc	3
ccs	2
cts	2
cp	1
crap	1
rs	10
c	0
b	0
f	0

<?php

namespace Simply\Database\Connection;

use Simply\Database\Connection\Provider\ConnectionProvider;

/**
 * Provides the basic functionality to operate on a MySQL database.
 * @author Riikka Kalliomäki <[email protected]>
 * @copyright Copyright (c) 2018 Riikka Kalliomäki
 * @license http://opensource.org/licenses/mit-license.php MIT License
 */
class MySqlConnection implements Connection
{
    /** @var ConnectionProvider The provider for the PDO connection */
    private $provider;

    /**
     * MySqlConnection constructor.
     * @param ConnectionProvider $provider The provider for the PDO connection
     */
    public function __construct(ConnectionProvider $provider)
    {
        $this->provider = $provider;
    }

    public function getConnection(): \PDO
    {
        return $this->provider->getConnection();
    }

    public function insert(string $table, array $values, & $primaryKey = null): \PDOStatement
    {
        $parameters = [];
        $result = $this->query($this->formatQuery([
            'INSERT INTO' => sprintf('%s (%s)', $this->formatTable($table), $this->formatFields(array_keys($values))),
            'VALUES' => $this->formatParameters($values, $parameters),
        ]), $parameters);

        if ($primaryKey !== null) {
            $primaryKey = $this->getConnection()->lastInsertId();
        }

        return $result;
    }

    public function select(
        array $fields,
        string $table,
        array $where,
        array $orderBy = [],
        int $limit = null
    ): \PDOStatement {
        $parameters = [];

        return $this->query($this->formatQuery([
            'SELECT' => $this->formatFields($fields),
            'FROM' => $this->formatTable($table),
            'WHERE' => $where ? $this->formatConditions($where, $parameters) : '',
            'ORDER BY' => $this->formatOrder($orderBy),
            'LIMIT' => $orderBy ? $this->formatLimit($limit, $parameters) : '',
        ]), $parameters);
    }

    public function update(string $table, array $values, array $where): \PDOStatement
    {
        $parameters = [];

        return $this->query($this->formatQuery([
            'UPDATE' => $this->formatTable($table),
            'SET' => $this->formatAssignments($values, $parameters),
            'WHERE' => $this->formatConditions($where, $parameters),
        ]), $parameters);
    }

    public function delete(string $table, array $where): \PDOStatement
    {
        $parameters = [];

        return $this->query($this->formatQuery([
            'DELETE FROM' => $this->formatTable($table),
            'WHERE' => $this->formatConditions($where, $parameters),
        ]), $parameters);
    }

    public function formatFields(array $fields, string $table = '', string $prefix = ''): string
    {
        if ($fields === []) {
            throw new \InvalidArgumentException('No fields provided for the query');
        }

        $format = '%2$s';

        if ($table !== '') {
            $format = '%1$s.' . $format;
        }

        if ($prefix !== '') {
            $format .= ' AS %3$s';
        }

        return implode(', ', array_map(function (string $field) use ($format, $table, $prefix): string {
            return sprintf(
                $format,
                $this->escapeIdentifier($table),
                $this->escapeIdentifier($field),
                $this->escapeIdentifier($prefix . $field)
            );
        }, $fields));
    }

    public function formatTable(string $table, string $alias = ''): string
    {
        if ($table === '') {
            throw new \InvalidArgumentException('No table provided for the query');
        }

        if ($alias !== '') {
            return sprintf('%s AS %s', $this->escapeIdentifier($table), $this->escapeIdentifier($alias));
        }

        return $this->escapeIdentifier($table);
    }

    /**
     * Formats the conditions for the SQL query.
     * @param array $conditions The conditions for the SQL query
     * @param array $parameters Array of parameters to fill
     * @return string Formatted conditions for the query
     */
    private function formatConditions(array $conditions, array & $parameters): string
    {
        if ($conditions === []) {
            throw new \InvalidArgumentException('No conditions provided for the query');
        }

        $clauses = [];

        foreach ($conditions as $field => $value) {
            $clauses[] = $this->formatClause($field, $value, $parameters);
        }

        return implode(' AND ', $clauses);
    }

    /**
     * Formats a single condition for the SQL query.
     * @param string $field The name of the field
     * @param mixed $value Expected value for the fields
     * @param array $parameters Array of parameters to fill
     * @return string The formatted condition for the query
     */
    private function formatClause(string $field, $value, array & $parameters): string
    {
        $escaped = $this->escapeIdentifier($field);

        if (\is_array($value)) {
            if (\in_array(null, $value, true)) {
                $value = array_filter($value, function ($value): bool {
                    return $value !== null;
                });

                if ($value !== []) {
                    $placeholders = $this->formatParameters($value, $parameters);
                    return "($escaped IN $placeholders OR $escaped IS NULL)";
                }

                return "$escaped IS NULL";
            }

            $placeholders = $this->formatParameters($value, $parameters);
            return "$escaped IN $placeholders";
        }

        if ($value === null) {
            return "$escaped IS NULL";
        }

        $parameters[] = $value;
        return "$escaped = ?";
    }

    /**
     * Formats a list of parenthesis enclosed parameters for an SQL query.
     * @param array $values List of values
     * @param array $parameters Array of parameters to fill
     * @return string The formatted parenthesis enclosed list of parameter placeholders
     */
    private function formatParameters(array $values, array & $parameters): string
    {
        array_push($parameters, ... array_values($values));
        return sprintf('(%s)', implode(', ', array_fill(0, \count($values), '?')));
    }

    /**
     * Formats the order clause for a select query.
     * @param array $order The order for the select query
     * @return string The formatted ORDER BY clause for the query
     */
    private function formatOrder(array $order): string
    {
        $clauses = [];

        foreach ($order as $field => $direction) {
            $clauses[] = sprintf('%s %s', $this->escapeIdentifier($field), $this->formatDirection($direction));
        }

        return implode(', ', $clauses);
    }

    /**
     * Returns the appropriate SQL sorting order for the sorting constant.
     * @param int $order The sorting constant
     * @return string The SQL representation for the given sorting order
     */
    private function formatDirection(int $order): string
    {
        if ($order === self::ORDER_ASCENDING) {
            return 'ASC';
        }

        if ($order === self::ORDER_DESCENDING) {
            return 'DESC';
        }

        throw new \InvalidArgumentException('Invalid sorting direction');
    }

    /**
     * Formats the LIMIT clause for a select query.
     * @param int|null $limit The maximum number of rows to return or null for no limit
     * @param array $parameters Array of parameters to fill
     * @return string The formatted LIMIT clause for the select query
     */
    private function formatLimit(?int $limit, array & $parameters): string
    {
        if ($limit === null) {
            return '';
        }

        $parameters[] = $limit;
        return '?';
    }

    /**
     * Formats value assignments in UPDATE query.
     * @param array $values Associate array of columns and values
     * @param array $parameters Array of parameters to fill
     * @return string The formatted list of assignments for the UPDATE query
     */
    private function formatAssignments(array $values, array & $parameters): string
    {
        if ($values === []) {
            throw new \InvalidArgumentException('No values provided for the query');
        }

        $assignments = [];

        foreach ($values as $field => $value) {
            $assignments[] = sprintf('%s = ?', $this->escapeIdentifier($field));
            $parameters[] = $value;
        }

        return implode(', ', $assignments);
    }

    /**
     * Escapes a MySQL query identifier.
     * @param string $identifier The identifier to escape
     * @return string The escaped identifier
     */
    private function escapeIdentifier(string $identifier): string
    {
        return "`$identifier`";
    }

    /**
     * Formats an arbitrary SQL query based on given clauses.
     * @param array $clauses Associative array of SQL clauses and their contents
     * @return string The complete formatted SQL query
     */
    private function formatQuery(array $clauses): string
    {
        $parts = [];

        foreach ($clauses as $clause => $value) {
            if ($value === '') {
                continue;
            }

            $parts[] = sprintf('%s %s', $clause, $value);
        }

        return implode(' ', $parts);
    }

    public function query(string $sql, array $parameters = []): \PDOStatement
    {
        $query = $this->getConnection()->prepare($sql);

        if (! $query instanceof \PDOStatement) {
            throw new \UnexpectedValueException('Unexpected value returned by prepare query');
        }

        foreach ($parameters as $name => $value) {
            $this->bindQueryParameter($query, \is_int($name) ? $name + 1 : $name, $value);
        }

        $query->execute();

        return $query;
    }

    /**
     * Binds an SQL query parameter to the PDO statement fo the query.
     * @param \PDOStatement $query The PDO statement for binding the value
     * @param int|string $name The name of the placeholder
     * @param mixed $value The value to bind
     * @return bool True if the binding was successful, false if not
     */
    private function bindQueryParameter(\PDOStatement $query, $name, $value): bool
    {
        switch (true) {
            case \is_string($value):
                return $query->bindValue($name, $value, \PDO::PARAM_STR);
            case \is_float($value):
                return $query->bindValue($name, var_export($value, true), \PDO::PARAM_STR);
            case \is_int($value):
                return $query->bindValue($name, $value, \PDO::PARAM_INT);
            case \is_bool($value):
                return $query->bindValue($name, $value ? 1 : 0, \PDO::PARAM_INT);
            case $value === null:
                return $query->bindValue($name, null, \PDO::PARAM_NULL);
            default:
                throw new \InvalidArgumentException('Invalid parameter value type');
        }
    }
}


1		<?php
2
3		namespace Simply\Database\Connection;
4
5		use Simply\Database\Connection\Provider\ConnectionProvider;
6
7		/**
8		* Provides the basic functionality to operate on a MySQL database.
9		* @author Riikka Kalliomäki <[email protected]>
10		* @copyright Copyright (c) 2018 Riikka Kalliomäki
11		* @license http://opensource.org/licenses/mit-license.php MIT License
12		*/
13		class MySqlConnection implements Connection
14		{
15		/** @var ConnectionProvider The provider for the PDO connection */
16		private $provider;
17
18		/**
19		* MySqlConnection constructor.
20		* @param ConnectionProvider $provider The provider for the PDO connection
21		*/
22	25	public function __construct(ConnectionProvider $provider)
23		{
24	25	$this->provider = $provider;
25	25	}
26
27	25	public function getConnection(): \PDO
28		{
29	25	return $this->provider->getConnection();
30		}
31
32	17	public function insert(string $table, array $values, & $primaryKey = null): \PDOStatement
33		{
34	17	$parameters = [];
35	17	$result = $this->query($this->formatQuery([
36	17	'INSERT INTO' => sprintf('%s (%s)', $this->formatTable($table), $this->formatFields(array_keys($values))),
37	16	'VALUES' => $this->formatParameters($values, $parameters),
38	16	]), $parameters);
39
40	15	if ($primaryKey !== null) {
41	15	$primaryKey = $this->getConnection()->lastInsertId();
42		}
43
44	15	return $result;
45		}
46
47	15	public function select(
48		array $fields,
49		string $table,
50		array $where,
51		array $orderBy = [],
52		int $limit = null
53		): \PDOStatement {
54	15	$parameters = [];
55
56	15	return $this->query($this->formatQuery([
57	15	'SELECT' => $this->formatFields($fields),
58	14	'FROM' => $this->formatTable($table),
59	13	'WHERE' => $where ? $this->formatConditions($where, $parameters) : '',
60	13	'ORDER BY' => $this->formatOrder($orderBy),
61	12	'LIMIT' => $orderBy ? $this->formatLimit($limit, $parameters) : '',
62	12	]), $parameters);
63		}
64
65	9	public function update(string $table, array $values, array $where): \PDOStatement
66		{
67	9	$parameters = [];
68
69	9	return $this->query($this->formatQuery([
70	9	'UPDATE' => $this->formatTable($table),
71	9	'SET' => $this->formatAssignments($values, $parameters),
72	8	'WHERE' => $this->formatConditions($where, $parameters),
73	7	]), $parameters);
74		}
75
76	6	public function delete(string $table, array $where): \PDOStatement
77		{
78	6	$parameters = [];
79
80	6	return $this->query($this->formatQuery([
81	6	'DELETE FROM' => $this->formatTable($table),
82	6	'WHERE' => $this->formatConditions($where, $parameters),
83	5	]), $parameters);
84		}
85
86	24	public function formatFields(array $fields, string $table = '', string $prefix = ''): string
87		{
88	24	if ($fields === []) {
89	2	throw new \InvalidArgumentException('No fields provided for the query');
90		}
91
92	22	$format = '%2$s';
93
94	22	if ($table !== '') {
95	4	$format = '%1$s.' . $format;
96		}
97
98	22	if ($prefix !== '') {
99	4	$format .= ' AS %3$s';
100		}
101
102		return implode(', ', array_map(function (string $field) use ($format, $table, $prefix): string {
103	22	return sprintf(
104	22	$format,
105	22	$this->escapeIdentifier($table),
106	22	$this->escapeIdentifier($field),
107	22	$this->escapeIdentifier($prefix . $field)
108		);
109	22	}, $fields));
110		}
111
112	26	public function formatTable(string $table, string $alias = ''): string
113		{
114	26	if ($table === '') {
115	1	throw new \InvalidArgumentException('No table provided for the query');
116		}
117
118	25	if ($alias !== '') {
119	4	return sprintf('%s AS %s', $this->escapeIdentifier($table), $this->escapeIdentifier($alias));
120		}
121
122	24	return $this->escapeIdentifier($table);
123		}
124
125		/**
126		* Formats the conditions for the SQL query.
127		* @param array $conditions The conditions for the SQL query
128		* @param array $parameters Array of parameters to fill
129		* @return string Formatted conditions for the query
130		*/
131	14	private function formatConditions(array $conditions, array & $parameters): string
132		{
133	14	if ($conditions === []) {
134	2	throw new \InvalidArgumentException('No conditions provided for the query');
135		}
136
137	12	$clauses = [];
138
139	12	foreach ($conditions as $field => $value) {
140	12	$clauses[] = $this->formatClause($field, $value, $parameters);
141		}
142
143	12	return implode(' AND ', $clauses);
144		}
145
146		/**
147		* Formats a single condition for the SQL query.
148		* @param string $field The name of the field
149		* @param mixed $value Expected value for the fields
150		* @param array $parameters Array of parameters to fill
151		* @return string The formatted condition for the query
152		*/
153	12	private function formatClause(string $field, $value, array & $parameters): string
154		{
155	12	$escaped = $this->escapeIdentifier($field);
156
157	12	if (\is_array($value)) {
158	3	if (\in_array(null, $value, true)) {
159		$value = array_filter($value, function ($value): bool {
160	1	return $value !== null;
161	1	});
162
163	1	if ($value !== []) {
164	1	$placeholders = $this->formatParameters($value, $parameters);
165	1	return "($escaped IN $placeholders OR $escaped IS NULL)";
166		}
167
168	1	return "$escaped IS NULL";
169		}
170
171	3	$placeholders = $this->formatParameters($value, $parameters);
172	3	return "$escaped IN $placeholders";
173		}
174
175	12	if ($value === null) {
176	1	return "$escaped IS NULL";
177		}
178
179	12	$parameters[] = $value;
180	12	return "$escaped = ?";
181		}
182
183		/**
184		* Formats a list of parenthesis enclosed parameters for an SQL query.
185		* @param array $values List of values
186		* @param array $parameters Array of parameters to fill
187		* @return string The formatted parenthesis enclosed list of parameter placeholders
188		*/
189	16	private function formatParameters(array $values, array & $parameters): string
190		{
191	16	array_push($parameters, ... array_values($values));
192	16	return sprintf('(%s)', implode(', ', array_fill(0, \count($values), '?')));
193		}
194
195		/**
196		* Formats the order clause for a select query.
197		* @param array $order The order for the select query
198		* @return string The formatted ORDER BY clause for the query
199		*/
200	13	private function formatOrder(array $order): string
201		{
202	13	$clauses = [];
203
204	13	foreach ($order as $field => $direction) {
205	3	$clauses[] = sprintf('%s %s', $this->escapeIdentifier($field), $this->formatDirection($direction));
206		}
207
208	12	return implode(', ', $clauses);
209		}
210
211		/**
212		* Returns the appropriate SQL sorting order for the sorting constant.
213		* @param int $order The sorting constant
214		* @return string The SQL representation for the given sorting order
215		*/
216	3	private function formatDirection(int $order): string
217		{
218	3	if ($order === self::ORDER_ASCENDING) {
219	2	return 'ASC';
220		}
221
222	2	if ($order === self::ORDER_DESCENDING) {
223	1	return 'DESC';
224		}
225
226	1	throw new \InvalidArgumentException('Invalid sorting direction');
227		}
228
229		/**
230		* Formats the LIMIT clause for a select query.
231		* @param int\|null $limit The maximum number of rows to return or null for no limit
232		* @param array $parameters Array of parameters to fill
233		* @return string The formatted LIMIT clause for the select query
234		*/
235	2	private function formatLimit(?int $limit, array & $parameters): string
236		{
237	2	if ($limit === null) {
238	1	return '';
239		}
240
241	2	$parameters[] = $limit;
242	2	return '?';
243		}
244
245		/**
246		* Formats value assignments in UPDATE query.
247		* @param array $values Associate array of columns and values
248		* @param array $parameters Array of parameters to fill
249		* @return string The formatted list of assignments for the UPDATE query
250		*/
251	9	private function formatAssignments(array $values, array & $parameters): string
252		{
253	9	if ($values === []) {
254	1	throw new \InvalidArgumentException('No values provided for the query');
255		}
256
257	8	$assignments = [];
258
259	8	foreach ($values as $field => $value) {
260	8	$assignments[] = sprintf('%s = ?', $this->escapeIdentifier($field));
261	8	$parameters[] = $value;
262		}
263
264	8	return implode(', ', $assignments);
265		}
266
267		/**
268		* Escapes a MySQL query identifier.
269		* @param string $identifier The identifier to escape
270		* @return string The escaped identifier
271		*/
272	26	private function escapeIdentifier(string $identifier): string
273		{
274	26	return "`$identifier`";
275		}
276
277		/**
278		* Formats an arbitrary SQL query based on given clauses.
279		* @param array $clauses Associative array of SQL clauses and their contents
280		* @return string The complete formatted SQL query
281		*/
282	16	private function formatQuery(array $clauses): string
283		{
284	16	$parts = [];
285
286	16	foreach ($clauses as $clause => $value) {
287	16	if ($value === '') {
288	12	continue;
289		}
290
291	16	$parts[] = sprintf('%s %s', $clause, $value);
292		}
293
294	16	return implode(' ', $parts);
295		}
296
297	17	public function query(string $sql, array $parameters = []): \PDOStatement
298		{
299	17	$query = $this->getConnection()->prepare($sql);
300
301	17	if (! $query instanceof \PDOStatement) {
302	1	throw new \UnexpectedValueException('Unexpected value returned by prepare query');
303		}
304
305	16	foreach ($parameters as $name => $value) {
306	16	$this->bindQueryParameter($query, \is_int($name) ? $name + 1 : $name, $value);
307		}
308
309	15	$query->execute();
310
311	15	return $query;
312		}
313
314		/**
315		* Binds an SQL query parameter to the PDO statement fo the query.
316		* @param \PDOStatement $query The PDO statement for binding the value
317		* @param int\|string $name The name of the placeholder
318		* @param mixed $value The value to bind
319		* @return bool True if the binding was successful, false if not
320		*/
321	16	private function bindQueryParameter(\PDOStatement $query, $name, $value): bool
322		{
323		switch (true) {
324	16	case \is_string($value):
325	15	return $query->bindValue($name, $value, \PDO::PARAM_STR);
326	16	case \is_float($value):
327	3	return $query->bindValue($name, var_export($value, true), \PDO::PARAM_STR);
328	16	case \is_int($value):
329	15	return $query->bindValue($name, $value, \PDO::PARAM_INT);
330	16	case \is_bool($value):
331	15	return $query->bindValue($name, $value ? 1 : 0, \PDO::PARAM_INT);
332	15	case $value === null:
333	14	return $query->bindValue($name, null, \PDO::PARAM_NULL);
334		default:
335	1	throw new \InvalidArgumentException('Invalid parameter value type');
336		}
337		}
338		}
339

simply-framework / database

MySqlConnection::escapeIdentifier() A last analyzed 2018-10-17 09:00 UTC

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like

MySqlConnection::escapeIdentifier() A
last analyzed 2018-10-17 09:00 UTC