SafeMySQL class

Type-hinted placeholders
Comments (12)

This is not actually a delusion but rather a solution.

SafeMysql is a PHP class to make Mysql queries safe and convenient.

safe because every dynamic query part goes into query via placeholder
convenient because it makes application code short and meaningful, without useless repetitions, making it Extra DRY

TL;DR: code on Github: http://github.com/colshrapnel/safemysql

Type-hinted placeholders

Is the main feature of the class, whe idea is taken from the Nikic's PDO wrapper.

And it's a really great step further from just ordinal placeholders used in prepared statements. Simply because dynamical parts of the query aren't limited to just scalar data! In the real life we have to add identifiers, arrays for IN operator, arrays for INSERT and UPDATE queries.

So - we need many different types of data formatting. Thus, we need the way to tell the driver how to format this particular data. Conventional prepared statements use toilsome and repeating bind_* functions. But there is a way more sleek and useful way - to set the type along with placeholder itself. It is not something new - well-known printf() function uses exactly the same mechanism. So, I hesitated not to borrow such a brilliant idea.

To implement such a feature, no doubt one have to have their own query parser. No problem, it's not a big deal. But the benefits are innumerable. Look at all the questions on Stackoverflow where developers trying in vain to bind a field name. Voila - with identifier placeholder it is as easy as adding a field value:

$field = $_POST['field'];
$value = $_POST['value'];
$sql   = "SELECT * FROM table WHERE ?n LIKE ?s";
$data  = $db->query($sql,$field,"%$value%");

Nothing could be easier!

Of course we will have placeholders for the common types - strings and numbers. But as we started inventing new placeholders - let's make some more!

Another trouble in creating prepared queries - arrays going to IN operator. Everyone is trying to do it their own way but the type-hinted placeholder makes it as simple as adding a string:

$array = array(1,2,3);
$data  = $db->query("SELECT * FROM table WHERE id IN (?a)",$array);

Same goes for such toilsome queries like INSERT and UPDATE.

And, of course, we have a set of helper functions to turn type-hinted placeholders into real brilliant, making almost every call to database as simple as 1 or 2 lines of code for all the regular real life tasks.

This class has been designed to serve 3 main purposes:

safe query building
easy and concise application code
ease to use and short API

Unfortunately, none of the PHP's bundled libraries serve a purpose of the database wrapper, nor provide sufficient safety. Writing API calls in the application code is messy and toilsome. So, we need a database wrapper class, to do all the dirty job. Yet we don't want a gargantuan framework for a such simple task as running queries against database.

There are some things to keep in mind before we start:

No doubts, first and foremost purpose of this class is safety and protection from SQL injections. But to I have to st most of people take injections wrong. They take it as a self. while it's just a side effect. side effect of improperly formatted query.

CRUD application example

Latest comments:

21.12.24 23:34
Paul Harding for (The only proper) PDO tutorial:
Fantastic articles, thank you very much for putting all this effort into them.
read more
17.12.24 07:19
Charles Roth for UPDATE query using PDO:
As described in one of your other excellent :-) phpdelusions pages, fetch() can do a (say)...
read more
16.12.24 20:08
Myles for (The only proper) PDO tutorial:
You only have silver on SQL-INJECTION. No one has gold.
read more
06.12.24 22:23
Charles Roth for (The only proper) PDO tutorial:
In your wonderful phpdelusions.net/pdo, you state that the types of results will always be...
read more
03.12.24 14:37
Adrian for Articles:
Have you written any books on PHP, or recommend any books? Thank you very much, and keep up the...
read more

Comments:

John, 20.09.24 00:54

Can the SafeMySQL class be used with MS SQL Server or Postgres as well? Or, is it strictly for MySQL?
Massimo Canova, 03.03.21 14:06
Great guide and wonderful SafeMySQL class !!! I am facing the following problem: I find myself having a query like this:
```
$table = 'test_1';
$id = 13;
$sql  = 'SELECT * FROM ?n WHERE `id` = ?i ';
if ($search_text != '') {
        $sql  .= " AND (field1 LIKE ?s ";
        $sql  .= " OR field2 LIKE ?s ";
        $sql  .= " OR field3 LIKE ?s ";
        $sql  .= " OR field4 LIKE ?s) ";
    }
   $sql .= "ORDER BY field5 ASC";
```
Not knowing IF the $search_text field will be set, how do I specify to $db-> getAll how many and what are the parameters to accept and what values should be assigned to it? I tried with: $db->getAll ($sql, $table, $id, $parm); // (where $parm is an array equal to the amount of fields ?s present in the SQL)

but it tells me that the number of parameters sent is less than the number of parameters required (because it sees it as a single field and not as an array of values). Is there a way to send an "N" number (not known) of Key->Value to $db->getAll ?
Neil Eames, 13.05.19 15:21

Hi, Are steps like type-hinted placeholders still useful with PDO? If so, I am thinking I will amend your safemysql class to process PDO instead of mysqli. Is that sensible? I really enjoy reading your work. Thanks. Neil.

Reply:
Hello Neil!

I would say it is sensible enough. I had such plans myself but I had to cancel them because I am not using this class anymore working with Doctrine at my job.

In fact, the type-hinted placeholders would be a good alternative to a full-featured query builder, letting arrays and identifiers to the native SQL/PDO.

On the other hand, over all those years, I re-thinked some premises, namely working with identifiers. Most of time we have to whitelist them, not just escape (what ?n and ?u do). So the only case left is an array, but I am not sure if a support for just a single placeholder type would worth the trouble. There is also ?i, but if you would translate custom placeholders to native ones, it won't be an issue, so ?i is not that important.

May be the best solution would be a combined approach: just a regular PDO wrapper that supports a single custom mark, like ?? that is to be processed and expanded to the set of native placeholders before calling prepare. I can think of some code to support this approach, feel free to ask if you would like to.
Zaur, 03.05.19 19:33

hi friend. i came across with you website when surfing on the net. i want to thank you ! you do very good job for people who want to learn pdo. i want to ask if pdf of that article available ? and if yes please send it to my email adress.
Chris, 04.09.17 23:08

Hi, Thanks for the great tool! :) I was wondering why there isn't any placeholder for boolean variables. Is this simply not added yet or is it because you should avoid booleans in databases in general? If I want to use booleans how would I go about adding this to the framework?

Thank you very much!
Reply:
Hello Chris!

The reason is simple: the boolean type in mysql is just an alias for tinyint(1). And thanks to PHP's loose typing, any boolean value will be correctly stored in mysql simply by using ?s placeholder.
```
$db->query("INSERT INTO booltest SET col=?s",true);
```
will work like a charm!
Patricio, 23.06.17 01:34

I would really appreciate if you could explain to me the behaviour of the parse() function in SafeMySql as I can't quite understand it. Thank you very much!
Reply:
Hello Patricio!

Thank you for a good question.

There are two main purposes for this function.

First one is a simple debugging: substituting any othe function with it, you can get a fully formatted SQL ready to run in database console. It could be extremely useful if you want to understand what is going wrong with your parameters. Like
```
echo $db->parse("INSERT INTO t (col) VALUES (?s)", $var);
```
The second one is a conditional query building.
Imagine there is an optional parameter, that could go into the query and could not. So you could use parse() method to create this conditional part:
```
if (!empty($_POST['price']))
{
    $age = $db->parse(" AND price <= ?s", $_POST['price']);
}
$sql = "SELECT * FROM t WHERE name=?s ?p";
$data = $db->getAll($sql, $_POST['name'], $age);
```
note that for the already parsed statement there is a distinct placeholder - ?p. It should be used only for this purpose and nothing else.

Hope it helps. Feel free to ask if you have any questions.
Greg, 02.06.17 18:15

FYI, your website's menu doesn't display on smaller mobile screens. I was reading on my phone today and there was no menu, so I did some testing and the menu didn't start showing up until I got to a landscape tablet @ 800x600 resolution. Below that and the menu simply doesn't display.
Harald, 29.05.17 06:57

Your site is great. I know that it requires a lot of energy, time, motivation and endurance to make it growing. I hope that you will do it. It is a long way to establish a website as a popular resource but there are so few websites with good content that it is worth the input even if the point of a return of invest is in a quite far future. So thank you for the effort and sharing your knowledge. I highly appreciate that. Please keep-up the good work :-)
Gio, 18.05.17 22:16
Hello, Could I ask how this become with safemysql class this code?

public static function getusers($user) { $role = new Class(); $sql = "SELECT t2.user_desc FROM role_user as t1 JOIN permissions as t2 ON t1.user_id = t2.user_id WHERE t1.user = :user"; $sth = $GLOBALS["DB"]->prepare($sql); $sth->execute(array(":user" => $user));
```
    while($row = $sth->fetch(PDO::FETCH_ASSOC)) {
        $user->permissions[$row["perm_desc"]] = true;
    }
    return $user;
}
```
Thanks for all your support !
nimNom, 03.05.16 18:30

I was also wondering why the database-library you used here is mysqli. you seem to be such a huge PDO fan - is there a good reason you have decided to use mysqli for your class?

Reply:
Thank you for a good question.

At the time I wrote it, I had little knowledge of PDO. Besides, Safemysql was initially a part of local framework, which was mysqli-based.

I am still a big fan of Safemysql, and prefer it over PDO for myself. But I learned that people are often reluctant to use a third-party lib, and prefer a built-in solution. So I do not offer Safemysql for the learners anymore, promoting PDO instead.
cypher, 28.01.16 18:06

I'm curious - why did you start a project in 11/2015 and decided to use the old mysql extension?

Reply:
Strictly speaking, it has been launched in 11/2012 and from the beginning were based on the new mysqli extension
maha, 25.11.15 05:43

hi, am doing project in PHP mysql but am having problem in the codes bugs problem . I dont want to switch to mysqli because of time . Can i complete my project in PHP mysql without having problem that make the system to not work despite injection problem ?

21.12.24 23:34
Paul Harding for (The only proper) PDO tutorial:
Fantastic articles, thank you very much for putting all this effort into them.
read more

17.12.24 07:19
Charles Roth for UPDATE query using PDO:
As described in one of your other excellent :-) phpdelusions pages, fetch() can do a (say)...
read more

16.12.24 20:08
Myles for (The only proper) PDO tutorial:
You only have silver on SQL-INJECTION. No one has gold.
read more

06.12.24 22:23
Charles Roth for (The only proper) PDO tutorial:
In your wonderful phpdelusions.net/pdo, you state that the types of results will always be...
read more

03.12.24 14:37
Adrian for Articles:
Have you written any books on PHP, or recommend any books? Thank you very much, and keep up the...
read more