ECA 236

1
ECA 236

• Open Source Server Side Scripting
• PHP MySQL

2
show source

• show_source( ) or highlight_file( )
• takes one parameter, the path to a .php filename
• prints a colored, highlighted version of the code
  in the browser
• CAUTION do not reveal sensitive information

lt?php highlight_file( name_of_file ) ?gt
3
web database architecture

• steps when a user accesses webpage/database
• browser sends HTTP request to server
• web server passes PHP code to PHP engine
• PHP engine parses the script
• PHP engine finds command to open database
  connection
• PHP opens connection to MySQL server ( local )
• MySQL receives query, checks users and
  privileges, processes query, returns results
• PHP engine finishes parsing script
• web server passes HTML to browser

4
PHP MySQL

• basic steps to query a database from the web
• check and filter data entered by user
• connect to appropriate database
• query the database
• retrieve the results
• present the results back to the user
• we will use the sitename database

5
connect to MySQL server

• mysql_connect( )
• connects to server
• prototype
• optional arguments
• host
• username
• password

reference mysql_connect( host, user,
password )
6
connect to MySQL server cont

• mysql_connect( )
• host is usually localhost
• connection to MySQL from local server
• user will have only privileges granted in mysql
  database
• if a connection is made, a link identifier is
  returned, with which we can reference the open
  connection

dbc mysql_connect( localhost, Web_User,
my1230 )
7
specify database

• once a connection has been established, you must
  identify a particular database to use
• similar to using the use keyword in the mysql
  monitor
• mysql_select_db( )
• Syntax

mysql_select_db( database_name, link_identifier
)
mysql_select_db( sitename, dbc )
8
security

• set the host, username, password, and database
  name to variables or CONSTANTS, save in a
  separate file, include this file in the script
• save with a .php extension
• to include or require file

define( DB_USER, Web_User ) define(
DB_PW, my1230 ) define( DB_HOST,
localhost ) define( DB_NAME, sitename )
require( db_params.php )
9
security cont

• connect to MySQL with the following
• test the connection from the server ( Xitami )
• if it works a blank page will load
• otherwise errors will display
• the same values we used in the mysql monitor
  should work in the PHP scripts

dbc mysql_connect( DB_HOST, DB_USER, DB_PW )
mysql_select_db( DB_NAME )
10
error handling

• even more important when connecting to a database
• probability for errors increases
• Common errors
• failure to connect to the database server
• failure to select a database
• inability to run a query
• no results returned

11
error handling cont

• PHP functions to handle MySQL errors
• mysql_errno( )
• returns the error number
• mysql_error( )
• returns the textual version of the error
• to handle errors gracefully
• _at_ sign to suppress error messages
• die( ) function

dbc _at_mysql_connect( DB_HOST, DB_USER, DB_PW )
or die( 'Could not connect to MySQL Error number
' . mysql_errno( ) . ' ' . mysql_error( ) )
12
simple query

• after connection to the server, and selection of
  a database, we can now execute queries
• Web_User has the following privileges
• SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,
  ALTER, INDEX, FILE
• mysql_query( )
• function for executing queries
• one parameter the query

13
simple query cont

• DO NOT place a semicolon inside your query
• INSERT, UPDATE, DELETE
• result will be either TRUE or FALSE
• SELECT
• result will contain the results of the query if
  successful
• result will be FALSE if query was unsuccessful

q 'SELECT first_name, last_name AS n FROM
users ORDER BY n'
result mysql_query( q )
14
close connection

• mysql_close( )
• one parameter, the link identifier
• this function is not required, but it is good
  programming to do so

mysql_close( dbc )
15
retrieving results

• mysql_fetch_array( )
• primary function for handling the rows returned
  from a SELECT query
• returns each row as an indexed or associative
  array
• two parameters
• result of the query, result in this example
• CONSTANT identifying what kind of array to return

16
retrieving results cont

• mysql_fetch_array( )
• CONSTANTs

CONSTANT EXAMPLE
MYSQL_ASSOC row column_name
MYSQL_NUM row 0
MYSQL_BOTH row 0 or row column_name
17
retrieving results cont

• mysql_fetch_array( )
• returns one row of data at a time as an array
• use within a loop that will run as long as rows
  are returned

while( row mysql_fetch_array( result,
MYSQL_ASSOC) ) // do something
18
retrieving results cont

• mysql_fetch_array( )
• return the first name, last name, and email
  addresses of all users in sitename
• alphabetize by last name

q 'SELECT FROM users ORDER BY last_name'
result mysql_query( q ) while( row
mysql_fetch_array( result, MYSQL_ASSOC )
) echo row'first_name' . " " .
row'last_name' . " " . row'email' . "ltbr
/gt"
19
retrieving results cont

• mysql_fetch_row( )
• equivalent to mysql_fetch_array( result,
  MYSQL_NUM ).
• mysql_fetch_assoc( )
• equivalent to mysql_fetch_array( result,
  MYSQL_ASSOC)
• when using associative arrays, the keys are case
  sensitive

20
validate user input

• if we have a form asking the user to input the
  following
• first name
• last name
• email address
• username
• password
• confirm password

21
validate user input cont

• earlier we had used the isset( ) function to
  check that a form element was not empty
• we can do something similar with empty( )
• empty( )
• returns true if the variable
• is zero
• is empty
• is NULL

22
validate user input cont

• empty( )
• we can use similar code to check that other
  variables are not empty

if (empty(_POST'last_name')) ln FALSE
else ln _POST'last_name'
23
validate user input cont

• if all values test TRUE we can use an if
  statement
• then add the user to the database

if( fn ln e u pw )
query "INSERT INTO users (username,
first_name, last_name, email, password,
registration_date) VALUES ('u', 'fn', 'ln',
'e', PASSWORD('p'), NOW( ) )" result
_at_mysql_query (query)
24
validate user input cont

• let the user know that the data has been added

if (result) echo 'ltpgtltbgtYou have been
registered!lt/bgtlt/pgt' else message
'ltpgtYou could not be registered due to a system
error. We apologize for any inconvenience.lt/pgtltpgt'
. mysql_error( ) . 'lt/pgt'
25
security

• review of ways to validate user input
• user superglobals to retrieve user input
• use regular expressions to validate user input
• trim( ) user input
• use the function strip_tags( ) to remove HTML and
  PHP tags
• use the function mysql_real_escape_string( ) to
  escape potentially troublesome characters

26
mysql_real_escape_string( )

• mysql_real_escape_string( )
• automatically escapes special character, such as
  single and double quotes, for use in a SQL
  statement
• for example
• a user enters data with an apostrophe, such as
  the last name OMalley
• without escaping the apostrophe, using OMalley
  in a SQL statement will throw an error

27
mysql_real_escape_string( ) cont

• rather than pulling the value from a for
  usingescape any potentially troublesome
  characters
• returns the value as

ln _POST'last_name'
ln mysql_real_escape_string(
_POST'last_name' )
O\Malley
28
stripslashes( )

• if necessary, remove the escaping backslashes
  with another function, stripslashes( ) echoes
• Magic Quotes
• when enabled, automatically escapes single and
  double quotes

str Is your name Shaun O\Malley?echo
stripslashes( str )
Is your name Shaun OMalley?
29
mysql_num_rows( )

• mysql_num_rows( )
• returns the number of rows retrieved by a SELECT
  query
• takes one parameter, the result set of the SELECT
  query

q 'SELECT last_name FROM users ORDER BY
last_name' result mysql_query( q ) echo
n mysql_num_rows( result )
30
mysql_num_rows( ) cont

• a simple test to see if a username already exists

q SELECT user_id FROM users WHERE username
u result mysql_query( q ) if(
mysql_num_rows( result ) 0 ) // insert
the data else echo That username is
already taken.
31
mysql_affected_rows( )

• mysql_affected_rows( )
• returns the number of rows affected by INSERT,
  UPDATE, or DELETE query
• takes one OPTIONAL parameter, the result set of
  the query
• if no parameter is specified, uses previous query

query "INSERT INTO users (username,
first_name, last_name, email, password,
registration_date) VALUES ('u', 'fn', 'ln',
'e', PASSWORD('p'), NOW( ) )"
result _at_mysql_query (query) echo Records
inserted . mysql_affected_rows( )
32
UPDATE

• to allow a user to change her password
• the first query returns the user_id if the
  username and password match data stored in the
  users table
• to compare the users submitted password,
  re-encrypt it, then compare with the stored value
• if the username and password match, exactly one
  record is returned
• assign this record to the row variable

33
UPDATE cont

• check for username / password match, return record

query "SELECT user_id FROM users WHERE
(username'u' AND passwordPASSWORD('p') )"
result _at_mysql_query (query) num
mysql_num_rows (result) if (num 1)
row mysql_fetch_array(result, MYSQL_NUM)
34
UPDATE cont

• if the username and password match, update the
  database with a new query
• verify the results of the query

query "UPDATE users SET passwordPASSWORD('np'
) WHERE user_idrow0" result
_at_mysql_query (query) // Run the query.
if (mysql_affected_rows( ) 1) echo
'ltpgtltbgtYour password has been changed.lt/bgtlt/pgt'
// end outer if