CODEWITHSUNDEEP
phparraysmysqlipdoescaping
Sutton
Sutton

Reputation: 310

PDO escaping with imploding array's

I am currently learning PDO (moved on from MySQLi) as MySQLi's prepared statements were, lets say 'sucky'. However, I've never fully experimented with PDO on this level before. I am attempting to create a PDO database class that include multiple common functions to build a query from the given information from the function.

This is my delete() function, that will be used to delete a row from a table:

public function delete( $table, $where = array(), $limit = '' ) {
    $holders = array();
    $params = array();
    foreach( $where as $field ) {
        $holders[] = '?';
    }

    foreach( $where as $field => $value ) {
        $value = $value;
        $clause[] = "$field = " . implode( '', array_values( $holders  ) ) . "";
        $params[] = $value;
    }

    $table = $this->prepend_table( $table );

    $sql = "DELETE FROM {$table} WHERE " . implode( 'AND ', $clause );
    if( !empty( $limit ) ) {
        $sql .= " LIMIT {$limit}";
    }

    try {
        $stmt = $this->connection->prepare( $sql );
        $stmt->execute( $params );
        return true;
    } catch( PDOException $e ) {
        die( 'Query Error: ' . $e->getMessage() );
        exit;
    }
}

As you can see, I am imploding the array's from the parameters inside the MySQL statement and then preparing and executing the query that has been built.

Now, in my previous question, someone mentioned about using implode() with preparing statements - like I have above, is very dangerous and that I should be using 'escape' for PDO. Now, I am aware that MySQLi had real_escape_string, but how would I go about doing this to work with my delete (and other functions) and to keep it as secure as possible.

As I'm new to PDO, if I'm missing any checks (or anything else to that I can do to keep it secure), please let me know - once it has finished I will be asking the code review base to review my class to ensure that it is secure.

Upvotes: 1

Views: 614

Answers (1)

Script47
Script47

Reputation: 14539

A word of caution since you are using prepared statements you won't need to as per this quote from PHP's documentation.

If you are using this function to build SQL statements, you are strongly recommended to use PDO::prepare() to prepare SQL statements with bound parameters instead of using PDO::quote() to interpolate user input into an SQL statement. Prepared statements with bound parameters are not only more portable, more convenient, immune to SQL injection, but are often much faster to execute than interpolated queries, as both the server and client side can cache a compiled form of the query.

So you can avoid the whole escaping process.

However, for your reference what you are looking for is PDO's quote function,

<?php
$conn = new PDO('sqlite:/home/lynn/music.sql3');

/* Simple string */
$string = 'Nice';

echo 'Unquoted string: ' . $string . '\n';
echo 'Quoted string: ' . $conn->quote($string) . '\n';
?>

Output

Unquoted string: Nice
Quoted string: 'Nice'

Upvotes: 1

Related Questions