How to Avoid Code Repetition When Using PHP SQL Prepared Statements?

How to Avoid Code Repetition with PHP SQL Prepared Statements

Prepared statements are crucial for preventing SQL injection attacks. However, they can lead to excessive code redundancy, especially in cases with numerous columns. To address this issue, there are several approaches to minimize repetition.

1. Raw PHP

This approach utilizes positional placeholders and omits the field names clause in the query, relying on default values for missing fields. For example:

$data = [$taskName, $startDate, $completedDate];
$sql = 'INSERT INTO tasks VALUES(null, ?, ?, ?)';
$db->prepare($sql)->execute($data);

2. Helper Function

One can create a helper function that accepts a table name and a data array with field names as keys and values as values. The function should utilize a helper function to properly escape field names for SQL injection prevention:

function prepared_insert($conn, $table, $data) {
    $keys = array_map('escape_mysql_identifier', array_keys($data));
    $fields = implode(",", $keys);
    $table = escape_mysql_identifier($table);
    $placeholders = str_repeat('?,', count($keys) - 1) . '?';
    $sql = "INSERT INTO $table ($fields) VALUES ($placeholders)";
    $conn->prepare($sql)->execute(array_values($data));
}

// Usage:
prepared_insert($db, 'tasks', [
    'task_name' => $taskName,
    'start_date' => $startDate,
    'completed_date' => $completedDate,
]);

3. Baby ORM

This approach uses object-oriented programming to further reduce repetition. A prototype class is created with common methods for all tables, and specific table classes are defined with the table name and list of columns. The insert code becomes:

$data = [
    'email' => 'email',
    'password' => 123,
    'name' => 'Fooster',
];

$userGateway = new UserGateway($pdo);

$id = $userGateway->create($data);
echo "Create: $id\n";

The prototype class:

class BasicTableGateway {
    public function create($data): int
    {
        $fields = $this->makeFieldList($data);
        $placeholders = str_repeat('?,', count($data) - 1) . '?';

        $sql = "INSERT INTO `$this->table` ($fields) VALUES ($placeholders)";
        $this->sql($sql,array_values($data));

        return $this->db->lastInsertId();
    }
}

The specific table class:

class UserGateway extends BasicTableGateway {
    protected $table = 'gw_users';
    protected $fields = ['email', 'password', 'name', 'birthday'];
}

The above is the detailed content of How to Avoid Code Repetition When Using PHP SQL Prepared Statements?. For more information, please follow other related articles on the PHP Chinese website!