WEB DESIGN
HienLTH – KhietLTN Web Design 1
PHP with MySQL
MSc. Luong Tran Ngoc Khiet
HienLTH – KhietLTN Web Design 2
Content
1. Basic data types in MySQL
2. Common commands in MySQL
3. Connect MySQL from PHP
4. The process of connecting to MySQL
5. Steps to access MySQL database
HienLTH – KhietLTN Web Design 3
1.Basic data types in MySQL
Type Description
maximum 255 characters, fixed length =
char(length) length
maximum 255 characters, dynamic length <=
varchar(length) length
text Maximum 65536 characters
int(length) -2.147.483.648 to +2.147.483.647
maximum length of digits in which dec
decimal(length,dec) decimal places
HienLTH – KhietLTN Web Design 4
1.Basic data types in MySQL
Type Description
enum(“option1”, self-defined set, maximum definition
“option2”,…) 65,535 values
date yyyy-mm-dd
time hh:mm:ss
datetime yyyy-mm-dd hh:mm:ss
HienLTH – KhietLTN Web Design 5
2.Common commands in MySQL
Type Description
CREATE Create CSDL or table
ALTER changing tables available
SELECT select data from the table
DELETE delete data from the table
DESCRIBE See a description of the table structure
INSERT INTO write values in the table
UPDATE Update data already in the table
DROP delete the entire table or database
HienLTH – KhietLTN Web Design 6
2.Common commands in 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 ...;
HienLTH – KhietLTN Web Design 7
2.Common commands in MySQL
ALTER TABLE tablename
(
ADD column datatype [NULL|NOT
NULL] [CONSTRAINTS],
CHANGE column columns datatype [NULL|NOT
NULL] [CONSTRAINTS],
DROP column,
...
);
HienLTH – KhietLTN Web Design 8
2.Common commands in MySQL
SELECT columnname, ...
FROM tablename, ...
[WHERE ...]
[UNION ...]
[GROUP BY ...]
[HAVING ...]
[ORDER BY ...];
VD: SELECT * FROM ketqua;
HienLTH – KhietLTN Web Design 9
2.Common commands in MySQL
DELETE FROM tablename
[WHERE ...];
VD:
DELETE FROM ketqua WHERE MaSV =
‘K29.103.010’
HienLTH – KhietLTN Web Design 10
2.Common commands in 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);
HienLTH – KhietLTN Web Design 11
2.Common commands in MySQL
UPDATE tablename
SET columname = value, ...
[WHERE ...];
VD:
UPDATE ketqua SET diem = 10 WHERE MaSV =
‘K29.103.010’
HienLTH – KhietLTN Web Design 12
2.Common commands in MySQL
DROP DATABASE | INDEX | PROCEDURE |
TABLE | TRIGGER | USER | VIEW itemname;
EG:
• Delete table SINHVIEN: DROP TABLE SinhVien
• Delete CSDL QLSV: DROP DATABASE QLSV;
HienLTH – KhietLTN Web Design 13
Command line communication
• Conect mysql server
mysql [-h hostname] [-P portnumber] -u username -p
mysql [-h hostname] [-P portnumber] --user=user --
password=pass
Enter the following command at mysql> command
prompt
Each SQL statement ends with a sign;
HienLTH – KhietLTN Web Design 14
Graphical communication
• Some common tools
SQLyog Enterprise
phpMyAdmin
MySQL Query Browser
MySQL Maestros
Navicat
MySQL Manager
HienLTH – KhietLTN Web Design 15
3. Connect MySQL from PHP
PHP script
mysqli PDO
Use class Use function
◼ mysqli mysqli
◼ mysqli_stmt
◼ mysqli_result
HienLTH – KhietLTN Web Design 16
Improved mysql library in PHP5
• Set up in php.ini
extension=php_mysqli.dll
• Advantages
– Supports object-oriented programming
– Supports database replication and distribution
– Compress and encrypt data on the connection
– Performance and code optimization
• Disadvantages
– Only works with MySQL databases
HienLTH – KhietLTN Web Design 17
4. The process of connecting to
MySQL
1. Open connection to database
2. Select database
3. Select charset (if necessary)
4. Database processing
5. Clean up
6. Close connection
HienLTH – KhietLTN Web Design 18
Step 1: Open connection to database
// OOP mysqli
$mysqli = new mysqli('hostname',
'username', 'password', 'dbname');
// mysqli
$link = mysqli_connect('hostname',
'username', 'password', 'dbname');
HienLTH – KhietLTN Web Design 19
Step 2: Select CSDL
// OOP mysqli
$mysqli->select_db('dbname');
// mysqli
mysqli_select_db($link, 'dbname');
HienLTH – KhietLTN Web Design 20
Step 3: Select encoding (if necessary)
// OOP mysqli
mysqli->query($link, "SET NAMES ‘character set’")
// mysqli
mysqli_query($link, "SET NAMES ‘character set’")
EG: SET NAMES UTF8
HienLTH – KhietLTN Web Design 21
Step 4: Database processing
• Query
// OOP mysqli
$result = mysqli->query(“query")
// mysqli
$result = mysqli_query($link, “query")
HienLTH – KhietLTN Web Design 22
Step 4: Database processing
• Retrieve data from the query
// 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)
HienLTH – KhietLTN Web Design 23
Step 5: Clean
// OOP mysqli
$result->close()
// mysqli
mysqli_free_result($result)
HienLTH – KhietLTN Web Design 24
Step 6: Close conection
// OOP mysqli
$mysqli->close()
// mysqli
mysqli_close($link)
HienLTH – KhietLTN Web Design 25
5. Steps to access MySQL
database
1. Make a connection to the database server
2. Select database
3. Build the query and execute the query
4. Processing returned results
5. Close the connection to the server
HienLTH – KhietLTN Web Design 26
Declare using MySQL database
•Declare database connection:
<?php
$dbhost = ‘localhost’;
$dbuser = 'root';
$dbpass = 'password';
$conn = mysqli_connect($dbhost, $dbuser,
$dbpass) or die (Không thể kết nối CSDL MySQL');
?>
•Select Working Database
$dbname =‘bookstore‘;
mysqli_select_db($dbname);
•Free up the Database
mysqli_close($conn);
HienLTH – KhietLTN Web Design 27
Declare using MySQL database
• Data query:
$result = mysqli_query(‘câu_truy_vấn’);
• Free up resources for queries
mysqli_free_result($result );
• Use query results:
mysqli_fetch_array($result);
mysqli_fetch_row($result);
mysqli_fetch_assoc($result);
• Use Vietnamese:
mysqli_query(“SET CHARACTER SET UTF8”);
Hay mysqli_query(“SET NAMES UTF8”);
HienLTH – KhietLTN Web Design 28
Note
• Function die ("String"): Issue the message and
terminate.
• With the above written method, die is executed
only when the previous command fails
• Necessary functions:
– mysqli_affected_rows(): the number of records is
affected by the preceding mysqli_query command.
– mysqli_error(): error message (if any)
– mysqli_errno(): error code
HienLTH – KhietLTN Web Design 29
PHP Data Objects
HienLTH – KhietLTN Web Design 30
PDO (PHP Data Object)
• Advantages:
– Applied from PHP 5.0 (built into PHP 5.1)
– Provides object-oriented communication
– Provides a consistent communication that allows data
movement between different database systems such as
Oracle, DB2, Microsoft SQL Server, PostgreSQL
• Disadvantages:
– No work on PHP versions <5.0.
– Do not take advantage of the advanced new features of
MySQL version 4.1.3 or higher, such as nested SQL
features.
HienLTH – KhietLTN Web Design 31
Connect CSDL PDO
try {
$dbh = new PDO($dsn,
$user, $password, $options);
} catch (PDOException $e) {
echo “Failed to connect:”
. $e->getMessage();
}
HienLTH – KhietLTN Web Design 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
HienLTH – KhietLTN Web Design 33
Connection manager
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;
HienLTH – KhietLTN Web Design 34
Take data
$dbh = new PDO($dsn);
$stmt = $dbh->prepare(
‘SELECT * FROM FOO’);
$stmt->execute();
while ($row = $stmt->fetch()) {
print_r($row);
}
HienLTH – KhietLTN Web Design 35
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
HienLTH – KhietLTN Web Design 36
Change of data
$deleted = $dbh->query(
“DELETE FROM FOO WHERE 1”);
$changes = $dbh->query(
“UPDATE FOO SET active=1 ”
. “WHERE NAME LIKE ‘%joe%’”);
HienLTH – KhietLTN Web Design 37
Smarter Queries
• Quoting is annoying, but essential
• PDO offers a better way
$stmt->prepare(‘INSERT INTO CREDITS
(extension, name) VALUES (:extension,
:name)’);
$stmt->execute(array(
‘:extension’ => ‘xdebug’,
‘:name’ => ‘Derick Rethans’
));
HienLTH – KhietLTN Web Design 38
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”;
}
}
HienLTH – KhietLTN Web Design 39
Transactions
try {
$dbh->beginTransaction();
$dbh->query(‘UPDATE …’);
$dbh->query(‘UPDATE …’);
$dbh->commit();
} catch (PDOException $e) {
$dbh->rollBack();
}
HienLTH – KhietLTN Web Design 40
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();
HienLTH – KhietLTN Web Design 41
Insert Multiple (1/2) - mysqli
• $sql = "INSERT INTO MyGuests (fullname, email)
VALUES ('John Doe', 'john@example.com');";
$sql .= "INSERT INTO MyGuests (fullname, email)
VALUES ('Mary Moe', 'mary@example.com');";
$sql .= "INSERT INTO MyGuests (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";
HienLTH – KhietLTN Web Design 42
Insert Multiple (2/2) - PDO
• // begin the transaction
$conn->beginTransaction();
• // our SQL statememtns
$conn->exec("INSERT INTO MyGuests (fullname, email)
VALUES ('John Doe', 'john@exam.com')");
$conn->exec("INSERT INTO MyGuests (fullname, email)
VALUES ('Mary Moe', 'mary@exam.com')");
$conn->exec("INSERT INTO MyGuests (fullname, email)
VALUES ('Julie Dooley', 'julie@exam.com')");
• // commit the transaction
$conn->commit();
HienLTH – KhietLTN Web Design 43
Check valid on Server
• There are 2 way:
– mysqli_real_escape_string (database only!)
$data = mysqli_real_escape_string($_POST[‘name’]);
– Use regular expressions
function test($value) {
$data = preg_match(“/[^A-Z]/”, $value);
if (!$data) alert (“valid”);
else alert (“invalid”);
}
HienLTH – KhietLTN Web Design 44
Validating data:
Character classes:
/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
HienLTH – KhietLTN Web Design 45
Validating data:
? 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)
HienLTH – KhietLTN Web Design 46
Risk to the end user
Typical attacks:
SQL injection
XSS scripting
session attacks
man in the middle
HienLTH – KhietLTN Web Design 47
SQL Injection
HienLTH – KhietLTN Web Design 48
SQL Injection
• It is a technique that allows attackers to
execute illegal SQL queries.
• By taking advantage of the vulnerability in
checking data entered in web applications.
HienLTH – KhietLTN Web Design 49
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 hien
Password 123
SELECT * FROM LOGIN WHERE username=‘hien’
AND password=‘123’;
HienLTH – KhietLTN Web Design 50
SQL injection attacks:
An SQL injection attack: input modifies query
Changes from secure to insecure
SELECT * FROM LOGIN WHERE username=$_POST[‘Username’]
AND password=$_POST[‘Password’];
Username hien’ --[space]
Password
SELECT * FROM LOGIN WHERE username=‘hien’
-- AND password=‘’;
SELECT * FROM LOGIN WHERE username=‘hien’;
HienLTH – KhietLTN Web Design 51
SQL injection attacks:
Captures all data on the table
“OR 1” clause is always true
SELECT * FROM LOGIN WHERE username=$_POST[‘Username’]
AND password=$_POST[‘Password’];
Username ’ OR 1 --[space]
Password
SELECT * FROM LOGIN WHERE username=‘’ OR 1 -- AND
password=‘’;
SELECT * FROM LOGIN;
HienLTH – KhietLTN Web Design 52
SQL injection attacks:
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 hien’ OR ‘x’=‘x’; --[space]
Password
SELECT * FROM LOGIN WHERE Username=‘hien’ OR ‘x’=‘x’;
SELECT * FROM LOGIN;
HienLTH – KhietLTN Web Design 53
SQL injection attacks:
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;
SELECT * FROM LOGIN;
HienLTH – KhietLTN Web Design 54
SQL injection attacks:
‘mysqli_query’: one query per command
$query1 = mysqli_query(“INSERT INTO tbluser VALUES (‘’,
‘$_POST[Username]’, ‘$_POST[LastName]’,
‘$_POST[FirstName]’)”);
SELECT * FROM tblemployee; DROP TABLE tblemployee; 0
without additional commands to reset the query
HienLTH – KhietLTN Web Design 55
SQL injection attacks:
An attack:
determine field and table
names
insert a new record
SELECT * FROM LOGIN WHERE Username=$_POST[‘Username’];
Username
x'; INSERT INTO members (‘User’, ‘pass’,
‘FirstName’, ‘LastName’) VALUES
(‘test01’,‘smelly’‘Steve’,‘Johnson’); --
HienLTH – KhietLTN Web Design 56
SQL injection attacks:
Delete/create a new table
SELECT * FROM LOGIN WHERE Username=$_POST[‘Username’];
Username x’; DROP table tblorder; --
x’; CREATE TABLE steve (id INT(5), name
VARCHAR(15)); --
SELECT * FROM LOGIN WHERE Username=‘Me’; DROP table
Order; --
HienLTH – KhietLTN Web Design 57
How to avoid
• 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
http://php.net/manual/en/mysqli.real-escape-string.php
HienLTH – KhietLTN Web Design 58
For example
HienLTH – KhietLTN Web Design 59
Q&A
HienLTH – KhietLTN Web Design 60
THE END
HienLTH – KhietLTN Web Design 61
Thank you!!!
HienLTH – KhietLTN Web Design 62
