Bài giảng Phát triển ứng dụng web - Kết nối PHP với MySQL

1Kết nối PHP với MySQL
Ths. Lương Trần Hy Hiến, khoa CNTT, ĐH Sư phạm TpHCM
Nội dung
1. Các kiểu dữ liệu cơ bản trong MySQL
2. Các lệnh thông dụng trong MySQL
3. Kết nối MySQL từ PHP
4. Quy trình kết nối vào MySQL
5. Các bước truy cập CSDL MySQL
2
31. Các kiểu dữ liệu cơ bản trong MySQL
Kiểu Mô tả
char(length) tối đa 255 ký tự, chiều dài cố định = length
varchar(length) tối đa 255 ký tự, chiều dài động <= length
text tối đa 65536 ký tự
int(length) -2.147.483.648 đến +2.147.483.647
decimal(length,dec)
tối đa length chữ số trong đó dec chữ số 
thập phân
4Kiểu Mô tả
enum(“option1”,
“option2”,)
tập hợp tự định nghĩa, tối đa 65.535 
giá trị
date yyyy-mm-dd
time hh:mm:ss
datetime yyyy-mm-dd hh:mm:ss
1. Các kiểu dữ liệu cơ bản trong MySQL
5Kiểu Mô tả
CREATE tạo CSDL hoặc bảng
ALTER thay đổi bảng có sẵn
SELECT chọn dữ liệu từ bảng
DELETE xóa dữ liệu khỏi bảng
DESCRIBE xem thông tin mô tả về cấu trúc bảng
INSERT INTO ghi giá trị vào bảng
UPDATE cập nhật dữ liệu đã có trong bảng
DROP xóa bảng hay toàn bộ CSDL
2. Các lệnh thông dụng trong MySQL
62. Các lệnh thông dụng trong MySQL
CREATE INDEX indexname ON tablename (column [ASC|DESC], ...);
CREATE PROCEDURE procedurename( [parameters] ) BEGIN ... END;
CREATE TABLE tablename
(
column datatype [NULL|NOT NULL] [CONSTRAINTS],
column datatype [NULL|NOT NULL] [CONSTRAINTS],
...
);
CREATE USER username[@hostname] [IDENTIFIED BY [PASSWORD] 
'password'];
CREATE [OR REPLACE] VIEW viewname AS SELECT ...; 
72. Các lệnh thông dụng trong MySQL
ALTER TABLE tablename
(
ADD column datatype [NULL|NOT NULL] 
[CONSTRAINTS],
CHANGE column columns datatype [NULL|NOT
NULL] [CONSTRAINTS],
DROP column,
...
); 
82. Các lệnh thông dụng trong MySQL
SELECT columnname, ...
FROM tablename, ...
[WHERE ...]
[UNION ...]
[GROUP BY ...]
[HAVING ...]
[ORDER BY ...];
VD: SELECT * FROM ketqua;
92. Các lệnh thông dụng trong MySQL
DELETE FROM tablename
[WHERE ...];
VD:
DELETE FROM ketqua WHERE MaSV = 
‘K29.103.010’
10
2. Các lệnh thông dụng trong MySQL
INSERT INTO tablename [(columns, ...)]
VALUES(values, ...);
INSERT INTO tablename VALUES(value1, value2, , 
valuen);
VD: 
INSERT INTO ketqua (mamon, diem) VALUES
(‘LTWEB’, 10);
INSERT INTO ketqua VALUES(‘’, ‘K29.103.010’, 
‘LTWEB’, 10);
11
2. Các lệnh thông dụng trong MySQL
UPDATE tablename
SET columname = value, ...
[WHERE ...];
VD:
UPDATE ketqua SET diem = 10 WHERE MaSV = 
‘K29.103.010’
12
2. Các lệnh thông dụng trong MySQL
DROP DATABASE | INDEX | PROCEDURE |
TABLE | TRIGGER | USER | VIEW itemname;
VD:
• Xóa bảng SINHVIEN: DROP TABLE SinhVien
• Xóa CSDL QLSV: DROP DATABASE QLSV;
13
Giao tiếp dòng lệnh
• Kết nối mysql server
mysql [-h hostname] [-P portnumber] -u username -p
mysql [-h hostname] [-P portnumber] --user=user --
password=pass
Nhập lệnh sau dấu nhắc lệnh mysql>
Mỗi lệnh SQL kết thúc bằng dấu ;
14
Giao tiếp đồ họa
• Một số công cụ thông dụng
SQLyog Enterprise
phpMyAdmin
MySQL Query Browser 
MySQL Maestros
Navicat
MySQL Manager
15
3. Kết nối MySQL từ PHP
PHP script
mysql mysqli
Sử dụng hàm
mysqli
Sử dụng lớp
 mysqli
 mysqli_stmt
 mysqli_result
Sử dụng hàm
mysql
PDO
16
Thư viện mysql cải tiến trong PHP5
• Thiết lập trong php.ini
extension=php_mysqli.dll
• Ưu điểm
– Hỗ trợ lập trình hướng đối tượng
– Hỗ trợ nhân bản và phân tán CSDL
– Nén và mã hóa dữ liệu trên kết nối
– Tối ưu hiệu năng và mã
• Nhược điểm
– Chỉ làm việc với CSDL MySQL
17
4. Quy trình kết nối vào MySQL
1. Mở kết nối đến CSDL
2. Chọn CSDL
3. Chọn bảng mã (nếu cần)
4. Xử lý CSDL
5. Dọn dẹp
6. Đóng kết nối
18
Bước 1: Mở kết nối đến CSDL
// OOP mysqli
$mysqli = new mysqli('hostname', 
'username', 'password', 'dbname');
// mysqli
$link = mysqli_connect('hostname', 
'username', 'password', 'dbname');
19
Bước 2: Chọn CSDL
// OOP mysqli
$mysqli->select_db('dbname');
// mysqli
mysqli_select_db($link, 'dbname');
20
Bước 3: Chọn bảng mã (nếu cần)
// OOP mysqli
mysqli->query($link, "SET NAMES ‘character set’")
// mysqli
mysqli_query($link, "SET NAMES ‘character set’")
VD: SET NAMES UTF8
21
Bước 4: Xử lý CSDL
• Truy vấn
// OOP mysqli
$result = mysqli->query(“query")
// mysqli
$result = mysqli_query($link, “query")
22
Bước 4: Xử lý CSDL (tt)
• Lấy dữ liệu từ truy vấn
// OOP mysqli
$row = $result->fetch_row()
$row = $result->fetch_assoc()
$row = $result->fetch_array(result_type)
// mysqli
$row = mysqli_fetch_row($result)
$row = mysqli_fetch_assoc($result)
$row = mysqli_fetch_array($result, result_type)
23
Bước 5: Dọn dẹp
// OOP mysqli
$result->close()
// mysqli
mysqli_free_result($result)
24
Bước 6: Đóng kết nối
// OOP mysqli
$mysqli->close()
// mysqli
mysqli_close($link)
5. Các bước truy cập CSDL MySQL
1. Tạo kết nối đến database server
2. Lựa chọn CSDL
3. Xây dựng truy vấn và thực hiện truy vấn
4. Xử lý kết quả trả về
5. Đóng kết nối đến server
25
26
Khai báo sử dụng CSDL MySQL
•Khai báo kết nối CSDL:
<?php
$dbhost = ‘localhost’;
$dbuser = 'root';
$dbpass = 'password'; 
$conn = mysqli_connect($dbhost, $dbuser, 
$dbpass) or die (Không thể kết nối CSDL 
MySQL');
?>
•Chọn Database làm việc
$dbname =‘bookstore‘;
mysqli_select_db($dbname);
•Giải phóng Database
mysqli_close($conn);
27
Khai báo sử dụng CSDL MySQL (tt)
• Truy vấn dữ liệu:
$result = mysqli_query(‘câu_truy_vấn’);
• Giải phóng tài nguyên truy vấn
mysqli_free_result($result );
• Sử dụng kết quả truy vấn:
mysqli_fetch_array($result);
mysqli_fetch_row($result);
mysqli_fetch_assoc($result);
• Sử dụng tiếng việt:
mysqli_query(“SET CHARACTER SET UTF8”);
Hay mysqli_query(“SET NAMES UTF8”);
28
Lưu ý
• Hàm die(“Chuỗi”): Đưa ra thông báo và 
kết thúc. 
• Với cách viết trên, die chỉ thực hiện khi lệnh 
trước nó không thành công
• Các hàm cần thiết:
– mysqli_affected_rows(): Số bản ghi bị tác động bởi 
lệnh mysqli_query liền trước.
– mysqli_error(): Thông báo lỗi (nếu có)
– mysqli_errno(): Mã lỗi
PHP Data Objects
29
PDO (PHP Data Object)
• Ưu điểm:
– Áp dụng từ PHP 5.0 (tích hợp sẵn trong PHP 5.1)
– Cung cấp giao tiếp hướng đối tượng
– Cung cấp một giao tiếp nhất quán cho phép lưu chuyển dữ 
liệu giữa các hệ cơ sở dữ liệu khác nhau như Oracle, DB2, 
Microsoft SQL Server, PostgreSQL
• Nhược điểm:
– Không làm việc trên PHP phiên bản < 5.0.
– Không tận dụng ưu điểm của các tính năng tiên tiến mới 
của MySQL phiên bản 4.1.3 trở lên, như tính năng lồng câu 
lệnh SQL.
30
31
Kết nối CSDL PDO
try {
$dbh = new PDO($dsn,
$user, $password, $options);
} catch (PDOException $e) {
echo “Failed to connect:” 
. $e->getMessage();
}
32
DSN format in PDO
• Driver:optional_driver_specific_stuff
– sqlite:/path/to/db/file
– sqlite::memory:
– mysql:host=name;dbname=dbname
– pgsql:native_pgsql_connection_string
– oci:dbname=dbname;charset=charset
– firebird:dbname=dbname;charset=charset;role=role
– odbc:odbc_dsn
33
Quản lý kết nối
try {
$dbh = new PDO($dsn, $user, $pw);
} catch (PDOException $e) {
echo “connect failed:” . $e->getMessage();
}
// use the database here
// 
// done; release the connection
$dbh = null;
34
Lấy dữ liệu
$dbh = new PDO($dsn);
$stmt = $dbh->prepare(
‘SELECT * FROM HangHoa’);
$stmt->execute();
while ($row = $stmt->fetch()) {
print_r($row);
}
35
Các kiểu duyệt (fetch type)
• $stmt->fetch(PDO_FETCH_BOTH)
– Array with numeric and string keys
– default option
• PDO_FETCH_NUM
– Array with numeric keys
• PDO_FETCH_ASSOC
– Array with string keys
• PDO_FETCH_OBJ
– $obj->name holds the ‘name’ column from the row
• PDO_FETCH_BOUND
– Just returns true until there are no more rows
36
Thay đổi dữ liệu
$deleted = $dbh->query(
“DELETE FROM HangHoa WHERE 
MaHH = 1”);
$changes = $dbh->query(
“UPDATE HangHoa SET active=1 ”
. “WHERE NAME LIKE ‘%coke%’”);
37
Prepared Statements
• Quoting is annoying, but essential
• PDO offers a better way
$stmt->prepare(‘INSERT INTO Account (email, 
fullname) VALUES (:email, :fullname)’);
$stmt->execute(array(
‘:email’ => ‘teo.tran@gmail.com’,
‘:fullname’ => ‘Trần Văn Tèo’
));
38
Prepared Statements
$stmt->prepare(‘INSERT INTO Account (email, fullname) 
VALUES (:email, :fullname)’);
$stmt->bindParam(':fullname', $fullname);
$stmt->bindParam(':email', $email);
// insert a row
$fullname = “Lý Tý";
$email = “lyty@example.com";
$stmt->execute();
// insert another row
$fullname = “Lý Tùng";
$email = “lytung@example.com";
$stmt->execute();
39
Binding for output
$stmt = $dbh->prepare(
"SELECT extension, name from CREDITS");
if ($stmt->execute()) {
$stmt->bindColumn(‘extension', $extension);
$stmt->bindColumn(‘name', $name);
while ($stmt->fetch(PDO_FETCH_BOUND)) {
echo “Extension: $extension\n”;
echo “Author: $name\n”;
}
}
40
Transactions
try {
$dbh->beginTransaction();
$dbh->query(‘UPDATE ’);
$dbh->query(‘UPDATE ’);
$dbh->commit();
} catch (PDOException $e) {
$dbh->rollBack();
}
Get ID of The Last Inserted Record
• $sql = "INSERT INTO Account (fullname, email) 
VALUES ('John Doe', 'john@exam.com')";
• MySQLi Procedural
if (mysqli_query($conn, $sql))
$last_id = mysqli_insert_id($conn);
• MySQLi Object-oriented
if ($conn->query($sql) === TRUE)
$last_id = $conn->insert_id;
• PDO
$conn->exec($sql);
$last_id = $conn->lastInsertId(); 41
Insert Multiple (1/2) - mysqli
• $sql = "INSERT INTO Account (fullname, email)
VALUES ('John Doe', 'john@example.com');";
$sql .= "INSERT INTO Account (fullname, email)
VALUES ('Mary Moe', 'mary@example.com');";
$sql .= "INSERT INTO Account (fullname, email) 
VALUES ('Julie Dooley', 'julie@example.com')";
• if ($conn->multi_query($sql) === TRUE)
echo "New records created successfully";
• if (mysqli_multi_query($conn, $sql))
echo "New records created successfully"; 42
Insert Multiple (2/2) - PDO
• // begin the transaction
$conn->beginTransaction();
• // our SQL statememtns
$conn->exec("INSERT INTO Account (fullname, 
email) VALUES ('John Doe', 'john@exam.com')");
$conn->exec("INSERT INTO Account (fullname, 
email) VALUES ('Mary Moe', 'mary@exam.com')");
$conn->exec("INSERT INTO Account (fullname, 
email) VALUES ('Julie Dooley', 'julie@exam.com')");
• // commit the transaction
$conn->commit();
43
Kiểm tra hợp lệ trên Server
• Có 2 cách :
– mysqli_real_escape_string (database only!)
– Sử dụng regular expressions
44
$data = mysqli_real_escape_string($_POST[‘name’]);
function test($value) {
$data = preg_match(“/[^A-Z]/”, $value);
if (!$data) alert (“valid”);
else alert (“invalid”);
}
Validating data:
/r/ defines a specific character
/./ matches any single character
/\./ matches dot (nokta)
[0-9] matches a named range of 
characters
[^a-z] NOT small letters
[a-zA-Z] multiple ranges of letters
/cat|dog/ cat or dog
^x must begin with ‘x’
x$ must end with ‘x’
\b word boundary
\B non-word boundary
45
Character classes:
Validating data:
46
? 0 or 1 duplications
* 0 or more duplications
+ 1 or more duplications
{n} exactly n times
{n, m} repeats between n and m times
{n,} repeats at least n times (n or more)
( ) grouping (like math)
Nguy cơ đối với người dùng cuối
47
Typical attacks:
SQL injection
XSS scripting
session attacks
man in the middle
SQL Injection
48
SQL Injection
• Là một kỹ thuật cho phép những kẻ tấn công 
thi hành các câu lệnh truy vấn SQL bất hợp 
pháp.
• Bằng cách lợi dụng lỗ hổng trong việc kiểm tra 
dữ liệu nhập trong các ứng dụng web.
49
50
SQL injection attacks:
Assume a login and SELECT query WHERE 
username=x and password=y
SELECT * FROM LOGIN WHERE username=$_POST[‘Username’] 
AND password=$_POST[‘Password’]; 
Username
Password
hien
123
SELECT * FROM LOGIN WHERE username=‘hien’ 
AND password=‘123’;
51
An SQL injection attack: input modifies query 
Changes from secure to insecure
SELECT * FROM LOGIN WHERE username=$_POST[‘Username’] 
AND password=$_POST[‘Password’]; 
Username
Password
hien’ --[space]
SELECT * FROM LOGIN WHERE username=‘hien’
-- AND password=‘’; 
SELECT * FROM LOGIN WHERE username=‘hien’;
SQL injection attacks:
52
Captures all data on the table
“OR 1” clause is always true
SELECT * FROM LOGIN WHERE username=$_POST[‘Username’] 
AND password=$_POST[‘Password’]; 
Username
Password
’ OR 1 --[space]
SELECT * FROM LOGIN WHERE username=‘’ OR 1 -- AND 
password=‘’; 
SELECT * FROM LOGIN;
SQL injection attacks:
53
Captures all data in table
X always equals X; entire table in recordset
If username AND password on same table
SELECT * FROM LOGIN WHERE username=$_POST[‘Username’] 
AND password=$_POST[‘Password’]; 
Username
Password
hien’ OR ‘x’=‘x’; --[space]
SELECT * FROM LOGIN WHERE Username=‘hien’ OR ‘x’=‘x’; 
SQL injection attacks:
SELECT * FROM LOGIN;
54
Attacking a number field (won’t work in example)
One always equals one (all fields returned)
SELECT * FROM LOGIN WHERE age=$_POST[‘age’];
Age 23 OR 1=1;
SELECT * FROM LOGIN WHERE age=23 OR 1=1;
SQL injection attacks:
SELECT * FROM LOGIN;
55
SQL injection attacks:
‘mysqli_query’: one query per command
SELECT * FROM tblemployee; DROP TABLE tblemployee; 
$query1 = mysqli_query(“INSERT INTO tbluser VALUES (‘’, 
‘$_POST[Username]’, ‘$_POST[LastName]’, 
‘$_POST[FirstName]’)”);
without additional commands to reset the query
56
SQL injection attacks:
An attack:
determine field and table names
insert a new record
Username
SELECT * FROM LOGIN WHERE Username=$_POST[‘Username’];
x'; INSERT INTO members (‘User’, ‘pass’, 
‘FirstName’, ‘LastName’) VALUES 
(‘test01’,‘smelly’‘Steve’,‘Johnson’); --
57
SQL injection attacks:
Delete/create a new table
Username
SELECT * FROM LOGIN WHERE Username=$_POST[‘Username’];
SELECT * FROM LOGIN WHERE Username=‘Me’; DROP table 
Order; --
x’; DROP table tblorder; --
x’; CREATE TABLE steve (id INT(5), name 
VARCHAR(15)); --
Cách phòng tránh
• Sử dụng hàm mysqli_real_escape_string
• Cú pháp:
string mysqli_real_escape_string ($link, string 
$unescaped_string)
==> chèn dấu \ vào trước các ký tự: \x00, \n, \r, \, 
', " và \x1a
58
Ví dụ
59
Q & A
THE END