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