MySQLi prepare with fetch_assoc, or is mysql_real_escape_string ok to use?

Question

I inherited a moderately large sized code base that makes extensive use of mysql_fetch_assoc and mysql_fetch_object, and doesn't have any security precautions preventing SQL injection. I decided that this was a Very Bad Thing^TM that needed fixing.

Initially, I intended on moving to mysqli prepare/bind_param/execute statements to handle DB queries. However, the following does not work:

$stmt = $GLOBALS['db']->prepare('SELECT * FROM Users WHERE Username=?');
$stmt->bind_param('s', $username);
$stmt->execute();

// Somehow retrieve the fetched row as an object (Doesn't work!)
return $stmt->fetch_assoc();

Question 1: Is there a way to use fetch_assoc or fetch_object using prepare/bind? I can refactor all the code, but it would be messy and take a very long time.

Question 2: If it is not possible to do this, is it just as effective from a security standpoint to use mysqli_query(), provided all inputs are properly escaped via mysql_real_escape_string()? Do you have any other suggestions?

score 1 · Accepted Answer · answered Oct 31 '11 at 20:06

1

$row = $stmt->fetch(PDO::FETCH_ASSOC) Ref: http://php.net/manual/en/pdostatement.fetch.php
You can use mysqli if you want. As long as you use the real_escape_string functions on ALL user data (including your own) that's going into SQL statements, then it's no less secure than using prepared statements - it's just much easier to miss a post and leave a hole open.

answered Oct 31 '11 at 20:06

Marc B

356,200
43
426
500

It looks like to do that, I'll have to use PDO instead of mysqli, but I think that should be fine. – elynnaie Oct 31 '11 at 20:27
There is `$result = $stmt->get_result()` for mysqli, which should return a regular result set you can do a while(fetchrow()) type thing on, but I've never used it, so not sure if that's exactly what you want – Marc B Oct 31 '11 at 20:33

score 0 · Answer 2 · edited May 23 '17 at 12:11

Replacing raw mysql with raw mysli makes very little sense.
in fact, all those doleful repetitive prepare/bind/execute/fetch being as ugly as mysql_query/fetch.

If you going to refactor your code anyway, you have to develop some library function, which will take a query and it's prameters as arguments and return an array of data, doing all the fetching inside.

as for the escaping all inputs via mysql_real_escape_string(), it is going to be an equivalent of magic quotes, which, at last, been acknowledged as a bad practice.

in fact, mysql_real_escape_string() doesnt make any data safe. it has nothing to do with safety at all. it's merely a formatting routine, and works only for strings. But there are other types to insert into query. For the detailed explanations refer to my prefious answer, How to include a PHP variable inside a MySQL insert statement

MySQLi prepare with fetch_assoc, or is mysql_real_escape_string ok to use?

2 Answers2