vi
new
+10
Avatar
Pham Tuan @pht
3.5K 131 34
Đã đăng vào thg 3 29, 2017 12:59 SA 29 phút đọc
1.4K

The only proper PDO tutorial

Editors' Choice

1. Giới thiệu

Original English version is of phpdelusions.net, I just want to practice and share it in Vietnamese.

Đã có rất nhiều tutorial về PDO, tuy nhiên không may mắn là hầu hết các bài viết đó đều không chỉ ra được lợi ích thực sự của PDO hay thậm chí còn đưa ra rất nhiều bad practices. Chỉ có 2 bài viết ngoại lệ đó là www.phptherightway.com và wiki.hashphp.org, nhưng 2 bài viết này vẫn chưa đầy đủ và thiếu đi rất nhiều thông tin quan trọng. Kết quả là một nữa các tính năng của PDO trở nên bị lãng quên và không bao giờ được sử dụng bởi phần lớn các PHP developer, những người đang cố gắng phát minh lại những thứ đã có sẵn trong PDO.

Khác với những tutorial khác, tutorial này được viết bởi một người có nhiều năm làm việc với PDO, đã moi móc từng ngõ ngách của nó và đã trả lời hàng nghìn câu hỏi liên quan trên Stack Overflow (người duy nhất có huy hiệu vàng trong chủ đề về PDO). Bài viết này sẽ giúp bạn có con đường đúng đắn, loại bỏ sự nhầm lẫn và các bad practices khi sử dụng PDO.

Tuy bài viết này được dựa trên MySQL driver, nhưng những thông tin được đề cập nhìn chung vẫn có thể áp dụng cho các driver khác mà PDO hỗ trợ.

2. Why PDO?

Điều đầu tiên, tại sao dùng PDO?

PDO là một Database Access Abstraction Layer. Mọi người đều biết PDO cung cấp một giao diện thống nhất cho việc kết nối nhiều loại CSDL khác nhau. Mặc dù tính năng này là rất tuyệt, nó thường không đem lại lợi ích gì cho một ứng dụng cụ thể cái mà chỉ có 1 loại database được sử dụng. Và cũng rất khó để chuyển database backend chỉ bằng 1 dòng config trong PDO, vì mỗi loại database thường có sự khác biệt trong cú pháp SQL (trừ khi bạn sử dụng một ngôn ngữ trung gian như DSL). Do vậy, đối với những lập trình viên LAMP bình thường, tính năng này của PDO là vô nghĩa đối với họ và PDO trong mắt họ chỉ là phiên bản phức tạp hóa của các hàm mysql(i)_query. Tuy nhiên, PDO không chỉ có thế, nó còn nhiều, nhiều lợi ích hơn nữa.

PDO không chỉ trừu tượng hóa database API mà còn đi kèm với nhiều hàm có sẵn mà có thể bạn đã lặp lại nó hàng trăm lần trong mỗi ứng dụng làm cho code của bạn trở nên vô cùng WET (write everything twice). Khác với mysql_ và mysqli_ đều là những API cấp thấp (low-level API), thường không được dùng để sử dụng trực tiếp mà chỉ được dùng để xây dựng một lớp trừu tượng cấp cao hơn, PDO là một lớp trừu tượng cấp cao. Nó vẫn chưa được hoàn thiện hết, nhưng ít nhất nó có thể dùng được.

Lợi ích thực sự của PDO đó là:

  • Bảo mật: sử dụng prepared statements
  • Dễ sử dụng: PDO cung cấp nhiều hàm helper cho các thao tác truy xuất thông thường
  • Tính sử dụng lại: API thống nhất cho nhiều CSDL

Ghi chú, mặc dù PDO có nhiều lợi ích như thế, tuy nhiên trong các ứng dụng web hiện đại có thể cân nhắc sử dụng các ORM hay Query Builder như Doctrine, Eloquent, RedBean, Yii Active Record. Aura.SQL là một ví dụ tốt về đóng gói PDO với nhiều tính năng bổ sung hơn.

Dù cách nào đi chăng nữa, hiểu về những thứ cơ bản trước là điều rất tốt. Vậy, chúng ta hãy bắt đầu.

3. Connecting. DSN

PDO cung cấp một phương thức kết nối thông qua DSN. Thay vì sử dụng 1 dòng cấu hình hay 1 danh sách các tùy chọn đơn giản, PDO chia thành 3 nhóm cấu hình:

  • Database driver, host, db (schema) name và charset, port, unix_socket được đặt trong DSN
  • username và password được đặt trong constructor
  • Các tùy chọn khác được đặt trong một danh sách

Định dạng của DSN:

      mysql:host=localhost;dbname=test;port=3306;charset=utf8
driver^    ^ colon         ^param=value pair    ^semicolon

Điều quan trọng là viết đúng theo format, với những tham số, giá trị và các dấu tách được mô tả trong manual.

VD kết nối đến mysql:

$host = '127.0.0.1';
$db   = 'test';
$user = 'user';
$pass = 'pass';
$charset = 'utf8';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opt = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];
$pdo = new PDO($dsn, $user, $pass, $opt);

Ghi chú dành cho những người hay sử dụng các hàm mysql_:

  • Các hàm mysql_ có thể được sử dụng ở bất cứ nơi nào trong code, trong khi đó PDO instance được lưu trong một biến thông thường, có nghĩa là nó có thể không thể sử dụng được trong các hàm khác, vì vậy để có thể sử dụng trong các hàm khác, có thể truyền qua tham số của hàm hoặc sử dụng các kỹ thuật nâng cao như IoC container.
  • Kết nối đến database server chỉ được thiết lập 1 lần. Không connect trong từng hàm. Không connect trong từng class. Nếu không sẽ có rất nhiều kết nối đến database server có thể làm treo server. Vì vậy, chỉ một PDO instance được tạo ra và sử dụng nó trong toàn bộ quá trình thực thi code.
  • Một điều quan trọng nữa đó là thiết lập charset bên trong DSN, đó là cách đúng duy nhất. Hãy quên việc sử dụng query SET NAMES một cách thủ công thông qua hàm query() hay qua option PDO::MYSQL_ATTR_INIT_COMMAND. Chỉ khi bạn đang sử dụng phiên bản PHP quá cũ (< 5.3.6) thì mới cần sử dụng query SET NAMES và nhớ là hãy tắt emulation mode.

4. Thực thi query. PDO::query

Có 2 cách để thực thi query trong PDO. Nếu không có biến nào được sử dụng trong query, bạn có thể sử dụng phương thức PDO::query(). Nó sẽ thực thi query và trả về một object thuộc class PDOStatement, đại khái nó giống như resource được return bởi hàm mysql_query(), đặc biệt là trong cách bạn lấy ra các dòng dữ liệu:

$stmt = $pdo->query('SELECT name FROM users');
while ($row = $stmt->fetch()) {
    echo $row['name'] . "\n";
}

5. Prepared statements. Tránh lỗi SQL Injections

PDO đã hỗ trợ việc sử dụng prepared statements một cách dễ dàng nhất, và đây là lý do chính cho việc bạn bị tước bỏ việc sử dụng hàm mysql_query() yêu quý và bị ném vào thế giới khó chịu của Data Objects. Prepared statements là cách đúng đắn duy nhất khi bạn thực thi các câu query có chứa các biến bên trong nó. Lý do tại sao nó thực sự quan trọng bạn có thể xem chi tiết tại The Hitchhiker's Guide to SQL Injection prevention.

Như vậy, trong mỗi query bạn muốn thực thi, nếu có ít nhất một biến được sử dụng bên trong câu query, bạn phải thay thế nó với 1 placeholder, tiếp theo là prepare query và sau đó truyền biến vào và thực thi. Chuyện thì dài nhưng kể ra thì nó cũng không có gì phức tạp cả. Trong hầu hết các trường hợp bạn chỉ cần thao tác với 2 hàm prepare() và execute().

Đầu tiên, bạn phải sửa lại câu query, sử dụng placeholder thay vào vị trí của các biến, ví dụ đoạn code sau:

$sql = "SELECT * FROM users WHERE email = '$email' AND status = '$status'";

sẽ được chuyển thành

$sql = 'SELECT * FROM users WHERE email = ? AND status = ?';

hoặc

$sql = 'SELECT * FROM users WHERE email = :email AND status = :status';

Ghi chú, PDO hỗ trợ placeholder theo vị trí (?) (positional placeholder) và theo tên (:email) (named placeholder), named placeholder luôn bắt đầu bằng dấu hai chấm : theo sau là tên theo quy tắc tên biến thông thường, chú ý không có dấu nháy nào được sử dụng ở bao quanh placeholder.

Với query có placeholder, bạn thực hiện prepare nó, sử dụng hàm PDO::prepare(), hàm này sẽ trả về PDOStatement giống như hàm PDO::query() ở trên nhưng nó không bao gồm bất cứ dữ liệu nào nào cả.

Cuối cùng, để thực thi query bạn thực hiện phương thức execute() trên đối tượng trả về của hàm prepare(), truyền biến thông qua mảng. Và sau đó, bạn có thể lấy ra dữ liệu của câu query nếu có.

$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ? AND status = ?');
$stmt->execute([$email, $status]);
$user = $stmt->fetch();
// or
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email AND status = :status');
$stmt->execute(['email' => $email, 'status' => $status]);
$user = $stmt->fetch();

Như bạn thấy, với positional placeholder, bạn truyền vào một mảng giá trị tương ứng với vị trí của placeholder. Trong khi đó, với named placeholder, bạn cần truyền vào một associative array với key, value tương ứng. Bạn không thể dùng lẫn lộn, positional placeholder và named placeholder trong 1 câu query.

Chú ý rằng, positional placeholder có thể làm cho code ngắn họn hơn nhưng bạn phải truyền vào mảng theo đúng thứ tự các giá trị placeholder. Còn named placeholder giúp code dễ hiểu hơn, có thể truyền vào các giá trị theo thứ tự bất kỳ. Ngoài ra, bạn không cần phải kèm theo dấu ':' trong key của mảng placeholder key value.

Một lưu ý nữa là các placeholder không được đặt trong dấu nháy mặc dù có thể giá trị của nó là string, database driver sẽ tự động làm việc đó.

6. Các phương pháp binding placeholder

Truyền dữ liệu vào hàm execute() nên được cân nhắc là phương pháp mặc định và thuận tiện nhất. Khi hàm này được sử dụng, tất cả placeholder value đều được bind ở dạng string, trong hầu hết các trường hợp, điều này là đúng và sẽ không gây bất cứ vấn đề gì.

Tuy nhiên, trong một số trường hợp việc khai báo kiểu dữ liệu một cách tường minh sẽ tốt hơn. VD:

  • LIMIT clause trong emulation mode hay những SQL clause mà không cho phép sử dụng string.
  • Các kiểu dữ liệu đặc biệt, như BIGINT hoặc BOOLEAN, yêu cầu chính xác loại dữ liệu (ghi chú, để bind một giá trị BIGINT với PDO::PARAM_INT web server của bạn cần cài đặt mysqlnd).

Bạn có thể sử dụng 2 hàm bindValue() và bindParam(). Hàm bindValue() thường được ưa thích hơn. Sự khác biệt của hàm bindParam() đó là giá trị được truyền vào theo tham chiếu:

public bool PDOStatement::bindParam(mixed $parameter, mixed &$variable)
public bool PDOStatement::bindValue(mixed $parameter, mixed $value)

// Chạy đúng
foreach ($params as $key => &$val) {
    $sth->bindParam($key, $val);
}

// Có lỗi, vì bindParam cần 1 tham chiếu
foreach ($params as $key => $val) {
    $sth->bindParam($key, $val);
}

// Không có lỗi
$sth->bindValue('name', 'my name');

// Có lỗi
$sth->bindParam('name', 'my name');

Hàm bindParam() thường được dùng khi bind giá trị cho store produce. VD:

// Call a stored procedure with an INOUT parameter
$colour = 'red';
$sth = $dbh->prepare('CALL puree_fruit(?)');
$sth->bindParam(1, $colour, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 12);
$sth->execute();
print("After pureeing fruit, the colour is: $colour");

7. Thực thi các câu query SELECT, INSERT, UPDATE, DELETE

Các câu query insert, update, delete không trả về dữ liệu , do đó bạn có thể viết ngắn gọn bằng cách chaining method:

$sql = "UPDATE users SET name = ? WHERE id = ?";
$pdo->prepare($sql)->execute([$name, $id]);

Tuy nhiên, nếu bạn muốn kiểm tra xem có bao nhiêu dòng bị ảnh hưởng bởi câu query thì bạn phải thực hiện lần lượt các bước prepare và execute.

$stmt = $pdo->prepare("DELETE FROM goods WHERE category = ?");
$stmt->execute([$cat]);
$deleted = $stmt->rowCount();

8. Lặp qua dữ liệu từ statement: foreach()

PDOStatement implement interface Traversable do đó bạn có thể sử dụng vòng lặp foreach để lấy ra dữ liệu.

$stmt = $pdo->query('SELECT name FROM users');
foreach ($stmt as $row) {
    echo $row['name'] . "\n";
}

Ghi chú, phương pháp này có tính chất memory-friendly, vì nó không load tất cả kết quả vào bộ nhớ một lúc mà nó thực hiện load từng hàng dữ liệu một.

9. Lấy một dòng duy nhất từ statement: fetch()

Để lấy một dòng dữ liệu từ database chúng ta sử dụng method fetch() cùa statement. Khi hàm này thực hiện, nó sẽ trả về 1 dòng trong tập dữ liệu trả về của statement và di chuyển con trỏ đến dòng tiếp theo trong tập dữ liệu trả về. Cấu trúc dữ liệu trả về tương ứng với từng kiểu fetch, mode mặc định được là PDO::FETCH_BOTH nếu bạn không khai báo tuỳ chọn PDO::ATTR_DEFAULT_FETCH_MODE khi khởi tạo đối tượng PDO. Các mode thường dùng bao gồm:

  • PDO::FETCH_NUM trả về mảng với key là số thứ tự của cột trong tập kết quả trả về và value là giá trị tương ứng
    Array
(
    [0] => apple
    [1] => red
)
  • PDO::FETCH_ASSOC trả về mảng kết hợp, với key là tên cột và value là giá trị của cột tương ứng
    Array
(
    [name] => apple
    [colour] => red
)
  • PDO::FETCH_BOTH trả về mảng kết hợp cả 2 mode ở trên, có nghĩa là dữ liệu trả về vừa có key là số thứ tự vừa có key là tên cột
    Array
(
    [name] => banana
    [0] => banana
    [colour] => yellow
    [1] => yellow
)
  • PDO::FETCH_OBJ trả về object thuộc stdClass
Kiểu dữ liệu trả về

Chỉ khi PDO được xây dựng dựa trên mysqlnd và emulation mode được set off, PDO sẽ trả về các kiểu dữ liệu int, float, string... tương ứng với kiểu dữ liệu của database. Ví dụ bạn có bảng cấu trúc bảng:

create table typetest (string varchar(255), `int` int, `float` float, `null` int);
insert into typetest values('foo', 1, 1.1, NULL);

Dữ liệu trả về sẽ có dạng:

array(4) {
    ["string"] => string(3) "foo"
    ["int"]    => int(1)
    ["float"]  => float(1.1)
    ["null"]   => NULL
}

Ngược lại, nó chỉ trả về dữ liệu ở kiểu string và null. Bạn cũng có thể áp đặt PDO chỉ trả về string và null bằng cách đặt tuỳ chọn:

$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, true);

10. Lấy dữ liệu từ statement: fetchColumn()

Đây là một hàm ngắn gọn giúp bạn lấy giá trị của 1 cột từ truy vấn, hàm này rất có ích nếu bạn select chỉ 1 field từ query:

// Getting the name based on id
$stmt = $pdo->prepare("SELECT name FROM table WHERE id = ?");
$stmt->execute([$id]);
$name = $stmt->fetchColumn();

// getting number of rows in the table utilizing method chaining
$count = $pdo->query("SELECT count(*) FROM table")->fetchColumn();

11. Lấy ra danh sách dữ liệu: fetchAll()

PDOStatement::fetchAll() trả về một mảng chứa tất cả dữ liệu trả về bởi câu query, từ điều này chúng ta có thể đưa ra kết luận:

  • Không nên sử dụng hàm này nếu có quá nhiều dữ liệu trả về, thay vào đó sử dụng vòng lặp để lấy từng hàng 1.
  • Hàm này có ích nhất khi dữ liệu lấy ra không để hiển thị ngay mà được dùng để truyền vào một template view nào đó.

Bạn có thể ngạc nhiên là nó hỗ trợ trả về dữ liệu ở khá nhiều định dạng khác nhau, mà có thể bạn chưa từng chú ý đến. Một số dạng hay dùng:

Plain array

Mặc định, hàm này trả về một mảng chứa các hàng dữ liệu ở các định dạng indexed-array, associative array hay object (PDO::FETCH_NUM, PDO::FETCH_ASSOC, PDO::FETCH_OBJ)

$data = $pdo->query('SELECT name FROM users')->fetchAll();
var_export($data);
/*
array (
    0 => array('John'),
    1 => array('Mike'),
    2 => array('Mary'),
    3 => array('Kathy'),
)*/
Mảng 1 chiều chứa giá trị của một cột
$data = $pdo->query('SELECT name FROM users')->fetchAll(PDO::FETCH_COLUMN);
/* array (
    0 => 'John',
    1 => 'Mike',
    2 => 'Mary',
    3 => 'Kathy',
)*/
Mảng 1 chiều key-value
$data = $pdo->query('SELECT id, name FROM users')->fetchAll(PDO::FETCH_KEY_PAIR);
/* array (
    104 => 'John',
    110 => 'Mike',
    120 => 'Mary',
    121 => 'Kathy',
)*/
Mảng các hàng với key là giá trị của unique column

Với tham số PDO::FETCH_UNIQUE, hàm fetchAll() sử dụng cột đầu tiên làm index cho mảng kết quả trả về. Thông thường cột đầu tiên sẽ là id, tuy nhiên tốt nhất bạn nên select rõ ràng ra từng cột.

$data = $pdo->query('SELECT * FROM users')->fetchAll(PDO::FETCH_UNIQUE);
/* array (
    104 => array (
        'name' => 'John',
        'car' => 'Toyota',
    ),
    110 => array (
        'name' => 'Mike',
        'car' => 'Ford',
    ),
    120 => array (
        'name' => 'Mary',
        'car' => 'Mazda',
    ),
    121 => array (
        'name' => 'Kathy',
        'car' => 'Mazda',
    ),
)*/

Câu query sau sẽ cho kết quả khác:

$data = $pdo->query('SELECT name, id, car FROM users')->fetchAll(PDO::FETCH_UNIQUE);
/* array (
    'John' => array (
        'id' => '104',
        'car' => 'Toyota',
    ),
    'Mike' => array (
        'id' => '110',
        'car' => 'Ford',
    ),
    'Mary' => array (
        'id' => '120',
        'car' => 'Mazda',
    ),
    'Kathy' => array (
        'id' => '121',
        'car' => 'Mazda',
    ),
)*/
Mảng các hàng được nhóm theo 1 cột nào đó

PDO::FETCH_GROUP sẽ nhóm các hàng có giá trị của cột đầu tiên giống nhau, và giá trị đó được dùng để index mảng.

$data = $pdo->query('SELECT sex, name, car FROM users')->fetchAll(PDO::FETCH_GROUP);
/*array (
    'male' => array (
        0 => array (
            'name' => 'John',
            'car' => 'Toyota',
        ),
        1 => array (
            'name' => 'Mike',
            'car' => 'Ford',
        ),
    ),
    'female' => array (
        0 => array (
            'name' => 'Mary',
            'car' => 'Mazda',
        ),
        1 => array (
            'name' => 'Kathy',
            'car' => 'Mazda',
        ),
    ),
)*/

Hàm này rất có ích với các trường hợp như "nhóm các sự kiện theo thời gian", "nhóm hàng hoá theo danh mục"...

Một số trường hợp thực tế:

Sử dụng hàm callback để format

Sử dụng tuỳ chọn PDO::FETCH_FUNC để format theo định dạng cụ thể, hàm callback nhận các tham số là giá trị của từng cột.

$data = $pdo->query('SELECT name, car FROM users')->fetchAll(PDO::FETCH_FUNC, function ($name, $car) {
    return $name . ' - ' . $car;
});
/*array (
    0 => 'John - Toyota',
    1 => 'Mike - Ford',
    2 => 'Mary - Mazda',
    3 => 'Kathy - Mazda',
)*/

Các trường hợp khác, xem thêm PDO Fetch Modes, Fetching objects with PDO.

12. Error handling

Mặc dù có một số tuỳ chọn cho việc bắt lỗi trong PDO, cách hợp lý nhất đó là sử dụng tuỳ chọn PDO::ERRMODE_EXCEPTION. Bạn có thể khai báo trực tiếp option khi khởi tạo PDO như giới thiệu ở phần 2 hoặc thiết lập qua phương thức

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Khi có lỗi xảy ra, PDO sẽ ném ra 1 exception thuộc lớp PDOException.

Catching exception

Bạn chỉ nên bắt các exception trong PDO trong 2 trường hợp:

  • Nếu bạn đang viết 1 lớp đóng gói các chức năng của PDO và bạn muốn thêm vào một số thông tin khác vào error message như câu query gây ra lỗi. Trong trường hợp này, bạn có thể bắt PDOException, thu thập các thông tin khác và ném ra một Exception khác.
  • Trong một số trường hợp, bạn cần thực hiện thêm các thao tác khi có exception xảy ra, ví dụ:
    • Câu query không quan trọng, có thể bỏ qua error để thao tác các phần khác. Tuy nhiên bạn không nên tạo ra cho mình thói quen giấu đi các thông tin về lỗi.
    • Trường hợp bạn sử dụng transaction và phải thực hiện rollback database khi có lỗi xảy ra.
    • Bắt một số lỗi thông thường, nếu không phải là lỗi mà bạn đang chờ đợi thì ném lại exception đó để các phần khác xử lý. VD:
    try {
    $pdo->prepare("INSERT INTO users VALUES (NULL, ?, ?, ?, ?)")->execute($data);
} catch (PDOException $e) {
    if ($e->getCode() == 1062) {
        // Take some action if there is a key constraint violation, i.e. duplicate name
    } else {
        throw $e;
    }
}

13. Đếm số lượng rows với PDO

PDO cung cấp một hàm để lấy thông tin về số lượng rows trả về bởi câu query, PDOstatement::rowCount(). Tuy nhiên bạn không nên sử dụng nó chỉ như một flag để check xem có dữ liệu nào được trả về từ database hay không. Trong trường hợp đó bạn đã có có sẵn dữ liệu rồi thông qua hàm fetch hay fetchAll và nó sẽ được dùng như flag luôn.

Bạn cũng không nên sử dụng nó trong trường hợp đếm số lượng rows trong database, thay vào đó sử dụng query SELECT count(*):

$count = $pdo->query("SELECT count(1) FROM t")->fetchColumn();

Tóm lại:

  • Nếu bạn muốn đếm số lượng rows trong database, hãy dùng SELECT count(*)
  • Nếu bạn muốn kiểm tra câu query có trả về kết quả nào hay không, hãy check kết quả trở về
  • Nếu bạn vẫn muốn biết có bao nhiêu rows trả về bởi 1 truy vấn SELECT, thì sử dụng rowCount() hoặc đơn giản là đếm mảng kết quả trả về từ hàm fetchAll() bằng hàm count() nếu có thể.

14. Affected rows and insert id

Hàm rowCount() được cho việc đếm số rows trả về bởi query SELECT và số lượng rows bị ảnh hưởng bởi các query UPDATE, DELETE.

Một câu hỏi thường gặp đó là, trong thực tế mysql sẽ không update các các row nếu giá trị mới truyền vào giống với giá trị cũ trong database. Do đó, số lượng rows bị ảnh hưởng có thể khác với số lượng rows thoả mãn mệnh đề WHERE. Có trường hợp, chúng ta cần giá trị này, có trường hợp cần giá trị kia. Bạn có thể thiết lập tuỳ chọn PDO::MYSQL_ATTR_FOUND_ROWS cho PDO để nó luôn trả về số lượng rows thoả mãn WHERE, tuy nhiên, thiết lập này được gắn với mỗi kết nối (connection-only), nên bạn không thể thay đổi nó sau khi kết nối đã được thiết lập.

Giá trị của một trường auto_increment trong mysql có thể lấy thông qua hàm PDO::lastInsertId. Có câu hỏi rằng, "hàm này có an toàn khi dùng trong môi trường đa người dùng, đa tiến trình không", câu trả lời là có, vì nó chỉ là một interface đến hàm mysql_insert_id() của MySQL C API và hàm này là per-connection basis (chỉ phụ thuộc vào kết nối hiện tại).

15. Prepared Statement và LIKE clause

Khi sử dụng placeholder với LIKE clause, bạn không thể bao quanh placeholder bởi các dấu nháy hay các dấu %, _.

Cách dùng sau là sai:

$stmt = $pdo->prepare("SELECT * FROM table WHERE name LIKE '%?%'");

Cách đúng:

$stmt = $pdo->prepare("SELECT * FROM table WHERE name LIKE ?");
$stmt->execute(['%abc%']);

Chú ý, PDO không escape các ký tự % và _, bạn nên từ kiểm tra và để ignore các trường hợp này nếu không nó sẽ trả về kết quả là toàn bộ dữ liệu trong bảng.

16. Prepared Statement và IN clause

Để sử dụng prepared statements với IN clause, bạn phải bind từng giá trị bên trong cặp dấu ngoặc đơn sau IN clause. Cách dùng sai:

$ids = [1, 2, 3];
$stmt = $pdo->prepare('SELECT * FROM users WHERE id IN (?)');
$stmt->execute([implode(',', $ids)]);

Cách đúng:

$ids = [1, 2, 3];
$inPlaceholders = str_repeat('?,', count($ids) - 1) . '?';
$stmt = $pdo->prepare('SELECT * FROM users WHERE id IN ($inPlaceholders)');
$stmt->execute($ids]);

Trong trường hợp, câu query có chứa các placeholder khác, bạn có thể dụng array_merge() để nối tất cả các tham số lại:

$arr = [1, 2, 3];
$in = str_repeat('?,', count($arr) - 1) . '?';
$stm = $pdo->prepare('SELECT * FROM table WHERE foo = ? AND column IN ($in) AND bar = ? AND baz = ?');
$params = array_merge([$foo], $arr, [$bar, $baz]);
$stm->execute($params);

Trong trường hợp bạn sử dụng named placeholder, bạn phải tạo ra các placeholder có dạng id1, id2,...

// other parameters that are going into query
$params = ["foo" => "foo", "bar" => "bar"];

$ids = [1, 2, 3];
$in = '';
foreach ($ids as $i => $id) {
    $key = ":id$i";
    $in .= "$key,";
    $inParams[$key] = $id; // collecting values into key-value array
}
$in = rtrim($in, ","); // :id0,:id1,:id2

$sql = "SELECT * FROM table WHERE foo = :foo AND id IN ($in) AND bar = :bar";
$stm = $db->prepare($sql);
$stm->execute(array_merge($params,$inParams)); // just merge two arrays
$data = $stm->fetchAll();

17. Prepared Statement và table names

Trên StackOverflow chúng ta có thể thấy hàng loạt PHP users đang viết những đoạn code PDO tai hại nhất, họ nghĩ rằng giá trị data là thứ duy nhất cần lọc, nhưng tất nhiên là không phải như vậy.

Không may mắn là PDO không hỗ trợ placeholder cho 1 định danh (tên bảng và tên các trường), vì vậy bạn phải format chúng một cách thủ công nếu bạn lấy chúng từ input của người dùng.

Với MySQL, để format một tên định danh, chúng ta cần phải tuân theo 2 quy tắc:

  • Escape các dấu back-ticks bên trong tên định danh bằng cách gấp đôi ký tự back-tick
  • Bao quanh tên định danh dấu back-tick ```
$table = '`' . str_replace('`', '``', $table) . '`';
/*
$table = 'table' => `table`
$table = '`table`' => ```table```
*/

Ngoài ra, việc check những tên định danh so với một danh sách các trường cho phép là điều rất quan trọng (whitelist attributes). Ví dụ:

$orders  = ['name', 'price', 'qty']; //field names
$key     = array_search($_GET['sort'], $orders); // see if we have such a name
$orderby = $orders[$key]; // if not, first one will be set automatically. smart enuf :)
$query   = "SELECT * FROM `table` ORDER BY $orderby"; // value is safe

Hoặc đối với các query INSERT/UPDATE:

$data = ['name' => 'foo', 'submit' => 'submit']; // data for insert, from $_POST for example
$allowed = ['name', 'surname', 'email']; // allowed fields
$values = [];
$set = '';
foreach ($allowed as $field) {
    if (isset($data[$field])) {
        $set .= '`' . str_replace('`', '``', $field) . '`' . ' = :$field, ';
        $values[$field] = $data[$field];
    }
}
$set = substr($set, 0, -2);

Đoạn code trên cho output sẽ chỉ gồm những field cho phép INSERT/UPDATE

// `name` = :foo
$stmt = $pdo->prepare("INSERT INTO users SET $set");
$stmt->execute($values);

18. Prepared Statement với LIMIT clause

Một vấn đề khác liên quan đến SQL LIMIT clause. Khi sử dụng PDO ở chế độ emulation (tùy chọn PDO::ATTR_EMULATE_PREPARES = true) (mặc định là true), PDO thay thế placeholder với giá trị thực sự của data, thay vì gửi nó đi một cách riêng biệt. Và với "lazy" binding (sử dụng array bên trong hàm execute()), PDO xem tất cả các placeholder đều là string, dẫn đến câu query được prepare LIMIT ?, ? sẽ trở thành LIMIT '10', '10', đây là 1 câu truy vấn sai cú pháp.

Có 2 cách để xử lý vấn đề này:

  • Cách thứ nhất là tắt chế độ emulation:

    $conn->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

    Và các placeholder có thể được bind trực tiếp bên trong execute()

    $conn->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
$stmt = $pdo->prepare('SELECT * FROM table LIMIT ?, ?');
$stmt->execute([$offset, $limit]);
$data = $stmt->fetchAll();

  • Cách thứ hai là bind các giá trị một cách tường minh với kiểu dữ liệu:

    $stmt = $pdo->prepare('SELECT * FROM table LIMIT ?, ?');
$stmt->bindParam(1, $offset, PDO::PARAM_INT);
$stmt->bindParam(2, $limit, PDO::PARAM_INT);
$stmt->execute();
$data = $stmt->fetchAll();

    Chú ý, PDO::PARAM_INT vì lý do nào đó mà nó không thực hiện ép kiểu của tham số, nên nếu bind value là một số nhưng kiểu string thì vẫn sẽ gây lỗi:

    $stmt = $pdo->prepare("SELECT 1 LIMIT ?");
$stmt->bindValue(1, "1", PDO::PARAM_INT);
$stmt->execute();

19. Transactions

Để thực hiện các transaction một cách chính xác, bạn chú ý phải đặt chế độ error ở dạng exception (PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION). Exception là điều cần thiết cho việc thực hiện transaction vì nó có thể được handle, vì thế khi có lỗi xảy ra chúng ta có thể biết được và thực hiện rollback toàn bộ quá trình.

Ví dụ về transaction:

try {
    $pdo->beginTransaction();
    $stmt = $pdo->prepare("INSERT INTO users (name) VALUES (?)");
    foreach (['Joe', 'Ben'] as $name) {
        $stmt->execute([$name]);
    }

    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollback();
    throw $e;
}

Chú ý mấy điều quan trọng:

  • Bạn phải bắt Exception chứ không phải PDOException, vì các lỗi thông thường có thể xảy ra làm chương trình bị dừng mà không thể rollback database
  • Bạn nên re-throw exception sau khi rollback (tuy nhiên cái này tùy thuộc vào logic xử lý của từng phần)
  • Cũng nên chắc chắn là database engine hỗ trợ transaction (với MySQL thì nó là InnoDB, MyISAM không hỗ trợ)

20. Thực thi Stored Procedures trong PDO

Có một điều cần lưu ý về store procedure mà mọi developer nên biết đó là: stored procedure có thể không trả về rowset nào hoặc 1 hoặc nhiều rowset.

Trường hợp không trả về rowset nào, nếu bạn gọi các method fetch(), fetchAll(), fetchColumn() thì có lỗi xảy ra. Vì thế bạn không thể thực hiện vòng lặp do ... while ($stmt->nextRowset()). Thay vào đó bạn phải kiểm tra xem nó có trả về rowset nào không, bằng hàm columnCount() hoặc rowCount().

Trong trường hợp query trả về nhiều rowset, nếu sau khi bạn thực thi query stored procedure sau đó thực hiện tiếp 1 query khác, thì cũng sẽ có lỗi xảy ra, bởi vì bạn phải lấy ra hết rowset trước.

$stmt = $pdo->query("CALL bar()");
if ($stmt->columnCount()) {
	do {
		$data = $stmt->fetchAll();
	    var_dump($data);
	} while ($stmt->nextRowset() && $stmt->columnCount());
}

Nếu bạn không cần lấy dữ liệu từ câu truy vấn stored procedure, bạn có thể dùng hàm $stmt->closeCursor() để giải phóng statement và thực hiện các câu query khác.

21. Thực thi nhiều query cùng lúc

Khi ở chế độ emulation, PDO có thể thực thi nhiều query trong 1 statement bằng cả 2 cách query() hoặc prepare()/execute(). Để lấy dữ liệu của từng câu truy vấn, bạn sử dụng PDOStatement::nextRowset:

$stmt = $pdo->prepare("SELECT ?; SELECT ?");
$stmt->execute([1, 2]);
do {
    $data = $stmt->fetchAll();
    var_dump($data);
} while ($stmt->nextRowset());

Bên trong vòng lặp, bạn có thể thao tác lấy thông tin của từng query như affected rows, last insert id or check error.

Chú ý rằng, khi thực hiện hàm execute() PDO sẽ chỉ report error cho câu query đầu tiên. Nếu error xảy ra ở các câu query kế tiếp, chỉ khi bạn lặp đến result của query đó thì PDO mới report error.

22. Emulation mode. PDO::ATTR_EMULATE_PREPARES

Một trong những tùy chọn trong config của PDO được bàn luận nhiều nhất đó là PDO::ATTR_EMULATE_PREPARES. Vậy nó là gì?

PDO có thể thực thi câu truy của bạn theo 2 cách:

  1. Nó có thể sử dụng real hay native prepared statements Khi prepare() được gọi, câu query của bạn bao gồm cả placeholder được gửi đến mysql mà chưa được bind value. Value sẽ được truyền lên sau, khi execute() được gọi.
  2. Nó có thể sử dụng emulated prepared statements, câu SQL được gửi đến database server đã được format trước, dữ liệu đã được bind. Trong quá trình này sẽ chỉ có 1 quá trình tác động đến database server đó là khi gọi hàm execute(). Một số driver (bao gồm mysql) được mặc định bật chế độ emulation.

Cả 2 cách đều có ưu và nhược điểm, nhưng đều an toàn nếu bạn sử dụng đúng cách. Chỉ có điều bạn phải chắc chắn sử dụng các phiên bản database, PHP, PDO được hỗ trợ và thực hiện việc set encoding trong DSN.

Các vấn đề khác:

  • Khi bật chế độ emulation
    • Bạn có thể sử dụng các named placeholder lặp lại
    $stmt = $pdo->prepare("SELECT * FROM t WHERE foo LIKE :search OR bar LIKE :search");
$stmt->execute(['search' => "%$search%"]);
    • Bạn có thể chạy đồng thời nhiều query trong 1 statement
    • Do native prepared statements chỉ support một số loại query nên bạn chỉ có thể chạy một số query ở chế độ emulation. Ví dụ, đoạn code sau chỉ chạy khi ở chế độ emulation, ở chế độ native nó sẽ báo lỗi.
    $stmt = $pdo->prepare("SHOW TABLES LIKE ?");
$stmt->execute(["%$name%"]);
var_dump($stmt->fetchAll());
  • Khi tắt chế độ emulation
    • Do không phải rắc với kiểu dữ liệu của placeholder do mysql có thể tự động chọn kiểu phù hợp, vì vậy chúng ta vẫn có thể truyền string vào LIMIT clause.

Dựa vào những điều nêu trên bạn có thể chọn một chế độ ưa thích hơn để sử dụng. Theo quan điểm của tác giả, tác giả có thể sẽ chọn các tắt chế độ emulation để tránh những phiền phức khi dùng với LIMIT, những vấn đề khác có thể bỏ qua.

PHP Database MySql

All rights reserved

Mục lục


Viblo
Hãy đăng ký một tài khoản Viblo để nhận được nhiều bài viết thú vị hơn.
Đăng kí