Emulated prepared statements VS prepared statements

This thread was split off from Splitting my query


Sorry I don’t think you are right here. Emulated Statements are as safe as real prepared statements. The only way to “hack” emulated statements is a man in the middle attack between PDO and DB and if you have such kind of attack, I guess prepared statements are your smallest problem.

This is a good post to this topic:

I would suggest the following solution as it is the best readable for me:

if ($_POST['search_name'] == '')
{
  $query = "SELECT client_id, name, phone, address, email FROM client";
} 
else 
{
  $query = "SELECT client_id, name, phone, address, email 
                  FROM client
                 WHERE name LIKE :searchKey
                      OR phone LIKE :searchKey
                      OR address LIKE :searchKey
                      OR email LIKE :searchKey;
}
$stmt = $pdo->prepare($query);
$stmt->bindValue('searchKey', trim($_POST['search_name']));
$stmt->execute();
$clients = $stmt->fetchAll();

btw, sometime OR statements in mysql are very slow (don’t ask me why but I had queries where it was faster to do a union with the exact same query with two different where instead of on where with a OR)

So you can also think about using this query:

$query = "SELECT client_id, name, phone, address, email 
                  FROM client
                 WHERE CONCAT(name, phone, address, email) LIKE :searchKey;
2 Likes

@Thallius, do you understand how the PDO emulator works? When it is being used, in the PDO driver, a value is either cast, for numerical data types or escaped and quoted, for string data types (string is the default type when you don’t specify any in explicit bindValue or bindParam statements and is always the type when using implicit binding, for an emulated prepared query, a true prepared query carries the actual value’s data type when using implicit binding - an admin on a different help forum, who is a member of the php documentation group, has put in a change request to have the emulator use the actual data types), literally searches and replaces the place-holders with the resulting values (which is why you can use the same named place-holder more than once with emulated prepared queries), then sends the resulting sql query statement to the database server, just as though you had performed these steps in your code using the PDO ->quote() statement (see the character set security warning in the PDO quote statement documentation) or back using the mysql/mysqli _real_escape_string() statements, for string data types, with the same security vulnerability that those have. If the character set that php uses is not the same as the database tables, sql special characters that do match your database table’s character encoding but don’t have any special meaning in the character set that php is using, will be sent to the database server as is and can break the sql query syntax, allowing sql injection.

This has nothing to do with a supposed man in the middle attack you found in a post on the web.

BTW, your proposed solution, with an unconditional bindValue() call, as has already been been written about in this thread, when there isn’t any corresponding place-holder (should) produce an error, since the number of place-holders and bound values doesn’t match (I’ve see queries silently fail when there are more bound inputs than place-holders). It also repeats part of the sql query, which both @benanamen and I have mentioned/avoided. And you should use CONCAT_WS, with a space between values, so that a concatenation that produces an edge value that matches the search term won’t result in a match.

@mabismad

Ok, then please give me a concrete example how SQL injection is possible with emulated statements which is not able with real statements.

Just one concrete example please…

The statement of how this is possible has been given. If you don’t know php’s history of trying to provide protection, on the php/driver side, with first magic quotes, then _escape_string statements, then _real_escape_string statements, and when and why those don’t work, you are not gong to understand how this is possible for emulated prepared queries.

A true prepared query, which completely separates the data values from the parsing of the sql query syntax, is the only 100% guaranteed method of preventing sql special characters in a data value from being able to break the sql query syntax, since it is not dependent on getting everything right between the character sets used in two different places. This is even more problematic when converting old applications, where database tables can have any existing mix of character encoding, combined with most people not even setting the character set when they make a database connection. Do you currently set the character set when you make a PDO connection, at all, and if so does it match the character set(s) of all your database tables?

Here is what PDO delusions has to say on the subject

2 Likes

@mabismad

Sorry, where is the concrete example?

I hear only bla bla bla…

You know, I’m not a hacker that has a library of values that would demonstrate this. Are you? Can you demonstrate that for all possible input values, for the condition that I have stated, of the character set php using not matching the database tables, that an emulated prepared query cannot be broken?

If you read the linked to php delusions information, it states the same thing, that you have to set the character set of the connection properly to make emulated prepared queries as safe as a true prepared query -

All you have to do is to set encoding in the DSN, as it shown in the example above, and your emulated prepared statements will be as secure as real ones.

The conclusion here appears to be that emulated and non emulated are just as safe provided the correct character set is passed into the DSN when connecting to MySQL.

Good to know!

2 Likes

Something I always do, not because of this reason but to be sure I use UTF-8 everywhere in my code/database etc.
There is nothing more annoying then searching hours over hours for a bug before you find out, that it is because of some idiot who is still working with windows character sets.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.