10

There is something I don't quite understand it at all which is prepare and query in mysqli.

This one is using mysqli::query to process the query and it has been known of lacking security:

public function fetch_assoc($query)
    {
        $result = parent::query($query);
        //$result = self::preparedStatement($query);
        if($result) 
        {
            return $result->fetch_assoc();
        } 
        else
        {
            # call the get_error function
            return self::get_error();
            # or:
            # return $this->get_error();
        }
    }

this is the one with prepare-bind-execute which has a better security I assume,

public function fetch_assoc_stmt($sql,$types = null,$params = null)
    {
        # create a prepared statement
        $stmt = parent::prepare($sql);

        # bind parameters for markers
        # but this is not dynamic enough...
        //$stmt->bind_param("s", $parameter);

        if($types&&$params)
        {
            $bind_names[] = $types;
            for ($i=0; $i<count($params);$i++) 
            {
                $bind_name = 'bind' . $i;
                $$bind_name = $params[$i];
                $bind_names[] = &$$bind_name;
            }
            $return = call_user_func_array(array($stmt,'bind_param'),$bind_names);
        }

        # execute query 
        $stmt->execute();

        # these lines of code below return one dimentional array, similar to mysqli::fetch_assoc()
        $meta = $stmt->result_metadata(); 

        while ($field = $meta->fetch_field()) { 
            $var = $field->name; 
            $$var = null; 
            $parameters[$field->name] = &$$var; 
        }

        call_user_func_array(array($stmt, 'bind_result'), $parameters); 

        while($stmt->fetch()) 
        { 
            return $parameters;  
        }

        # close statement
        $stmt->close();
    }

However, both of these two methods return the same result, 

$mysqli = new database(DB_HOST,DB_USER,DB_PASS,DB_NAME);

$sql = "
SELECT *
FROM root_contacts_cfm
ORDER BY cnt_id DESC
";
print_r($mysqli->fetch_assoc_stmt($sql));

print_r($mysqli->fetch_assoc($sql));

they print this:

Array
(
    [cnt_id] => 2
    [cnt_email1] => lau@xx.net
    [cnt_email2] => 
    [cnt_fullname] => Lau T
    [cnt_firstname] => Thiam
    [cnt_lastname] => Lau
    [cnt_organisation] => 
    [cnt_website] => 
    [cnt_biography] => 
    [cnt_gender] => 
    [cnt_birthday] => 
    [cnt_address] => 
    [cnt_postcode] => 
    [cnt_telephone] => 
    [cnt_note] => 
    [cnt_key] => 
    [cat_id] => 
    [tcc_id] => 
    [cnt_suspended] => 0
    [cnt_created] => 2011-02-04 00:00:00
    [cnt_updated] => 2011-02-04 13:54:36
)
Array
(
    [cnt_id] => 2
    [cnt_email1] => lau@xx.net
    [cnt_email2] => 
    [cnt_fullname] => Lau T
    [cnt_firstname] => Thiam
    [cnt_lastname] => Lau
    [cnt_organisation] => 
    [cnt_website] => 
    [cnt_biography] => 
    [cnt_gender] => 
    [cnt_birthday] => 
    [cnt_address] => 
    [cnt_postcode] => 
    [cnt_telephone] => 
    [cnt_note] => 
    [cnt_key] => 
    [cat_id] => 
    [tcc_id] => 
    [cnt_suspended] => 0
    [cnt_created] => 2011-02-04 00:00:00
    [cnt_updated] => 2011-02-04 13:54:36
)

You should have noticed that inside the method of fetch_assoc_stmt I don't use fetch_assoc at all. Probably no chance at all in using it as prepare uses a different way to return result.

So, my question is since using prepare is better than query, why should fetch_assoc exist at all? Shouldn't we just forget about it or shouldn't php.net have it deprecated?? It is the same for fetch_all - why should we have it in the first place!??

Thanks.

Run
  • 54,938
  • 169
  • 450
  • 748

2 Answers2

22

Prepared statements are preferable to plain SQL queries when you are using parameters to dynamically generate the query. In you example, your SQL contains no variables, so using a plain query or prepared statement are functionally equivalent.

When you must change the values of parameters, in the WHERE clause, for example, then prepared statements will give you added security:

...
WHERE col1 = ? AND col2 = ?

But when your query is simple and fixed, it may require less code to use $mysqli->query($sql) along with fetch_assoc(). Using direct queries rather than prepared statements is not a universally bad practice, as some might have you believe. When your query requires parameterization, or when the same query must be compiled and executed repeatedly, then you'll benefit from the prepared statement.

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
  • It should be noted that while prepared statements do handle repeated queries they also offer far superior injection protection, so i use them even when i don't need to repeat a query. – meteorainer May 18 '11 at 02:35
  • Do prepared statements offer improved security over: real_escape_string? – Steve Byrne Dec 29 '16 at 11:55
  • 1
    @StevenByrne I can't say they're inherently safer than _properly used_ escaping (depending on the database driver, and some other configuration factors) but prepared statements _force_ you to make sure variables have been parameterized, whereas you can easily forget to call a string escape function, or refactor code carelessly to disable it etc... See also http://stackoverflow.com/questions/1314521/how-safe-are-pdo-prepared-statements - in particular if prepared statement emulation is being done, statements aren't really safer, but are if the database itself is doing parameter substitution – Michael Berkowski Dec 29 '16 at 12:25
0

Sorry that this is not an answer, but I am not good enough to actually leave a comment.

It looks like there is a bug in your second function. Your code won't work correctly for queries that return more than one row. Shouldn't that return statement be:

while($stmt->fetch()) {

    //to dereference
    $row_copy = $parameters;
    $return_array[] = $row_copy;

}

And then the function should end with:

return $return_array;
Dave
  • 1,196
  • 15
  • 22