Part 5 - Server side Using databases with PHP Flashcards
describe the fllowing property of a results object
num_rows
this results object property can be used to return the number of rows that are present within a results object
describe the PHP MySQLi method
select_db()
this PHP MySQLi function is used to select a database and connect our database object to it
the pitfall of these is that they only protect the column values of a statement and not the keywords or table names
this means that if we want dynamic selection of keywords and table names by the user then we need another method to ensure the data from the user is not malicious
what is a
**pitfall of prepared statements **
and what does this ultimately mean
Syntax:
INSERT INTO table (v1, v1, v1)
VALUES (v2, v2, v2)
Parameters:
table: specifies the name of the table that you want to insert new data into.
v1: specifies the columns of the table that you want to populate with new data.
v2: specifies the actual data that will be values within the new row.
Example:
~~~
INSERT INTO users (first_name, last_name, email)
VALUES (‘John’, ‘Doe’, ‘john.doe@example.com’)
~~~
what is the syntax and params of the SQL statement
INSERT INTO
describe the method
query()
this method can be used to perform queries against a database
it Will return a result object if data is returned. Otherwise true or false depending on the success of the operation
what is the syntax and params of the method
prepare()
Syntax:
$mysqli -> prepare(query)
Param:
$mysqli - the database object/connection
Query - the sql query we wish to have prepared
We should consider this class of data as untrusted because it is possible that the data was not “cleaned” before it was stored.
If malicious JavaScript code was stored in the database and sent to the user, then a JavaScript attack could occur.
Therefore to make this data safe, we must escape any HTML output to the user to prevent this type of attack
Why should we consider
data from a database as untrusted
to achieve this in PHP, we can use the following code:
error_reporting(0);
ini_set('display_errors', 0);
notes:
1. The error_reporting() function sets which PHP errors are reported. The argument 0 turns off error reporting
2. The ini_set() function is used to set values of PHP’s configuration settings. Passing the argument (‘display_errors’, 0) disables error reporting for this script.”
How do we
turn off all error reporting in PHP
name 2
benefits of data only existing once inside a relational database
benefits include:
1. it is more efficient (for database resources and structure)
2. it mitigates errors (an update only has to occur in one place for example)
describe the SQL statement
SHOW COLUMNS
This SQL statement can be used to gather column information from a table.
Information returned will be a table where each row represents data of the column such as its name and data type
example:
// Check the value of the 'sort' key in the $data array
if ($data['sort'] == 'firstname') {
// If the value is 'firstname', set the $sort variable to 'firstname'
$sort = 'firstname';
}
if ($data['sort'] == 'lastname') {
// If the value is 'lastname', set the $sort variable to 'lastname'
$sort = 'lastname';
}
// Additional code goes here
// Use the $sort and $order variables to dynamically specify the column and order in an SQL ORDER BY clause
ORDER BY $sort $order
write an example of protecting against SQL injection when using dynamic column names or keywords from the user in an SQL statement
**During development **
where should errors be outputted?
during this time it may be easier to output errors to the web browser
describe the SQL clause
ORDER BY
This can be used to set the order of the output
we can specify which column should be used to define the sorting and whether it should be sorted ascending or descending
for this type of SQL statement we would expect the database to return an integer representing the number of affected rows
after executing an SQL DELETE statement what type of data could we expect returned from the database
using the SQL WHERE clause and the LIKE keyword write the statement that will
Will return any rows where the last_name column holds a value d anywhere in the entry
code xample:
WHERE last_name LIKE '%d%'
Syntax for SQL statement:
SELECT columns FROM table
Param
Columns - a comma separated list of columns we would like to select
Table - the table we would like to selct columns from
wha is the syntax and params of the SQL statement
SELECT
What values should we set in a script and use comparisons on the user’s data and our own values to protect against SQL injection attacks
this action should be performed on any user data that will dynamically change:
1. Database names
2. Table names
3. Column names
4. SQL keywords
this method can be used to perform queries against a database
it Will return a result object if data is returned. Otherwise true or false depending on the success of the operation
describe the method
query()
this is used so that we can prepare an SQL query for execution. This carries speed and security advantages.
On execution it will return A statement object on success. FALSE on failure
describe the PHP method
prepare()
describe the PHP method
bind_param()
this is used so that we can bind any variables we have to an already prepared statement
How can we
protect against SQL injection when using dynamic column names or keywords that are recieved from the user in an SQL statement?
We should NEVER use form data directly to populate column names or SQL keywords that we want to be dynamic. INSTEAD, we should use a comparison in the code between the form data and our expected values. If there is a match, we can explicitly set a variable ourselves that will be used to dynamically fill the SQL statement. This way, we can protect against SQL injection by ignoring any value that is not expected or proper
what is the syntax of the SQL statement
SHOW COLUMNS
syntax for SQL statement:SHOW COLUMNS FROM a_table
describe step 4 of creating prepared statements
Execute the prepared and bound statement
at this step we have a prepared and bound SQL statement and we are ready to execute it on the database.
Example:$stmt->execute();
this is the wild card symbol in SQL and is used with the LIKE keyword
LIKE ‘d%’ - would find any record that starts with the letter ‘d’
LIKE ‘%d’ - would find any record that ends with the letter ‘d’.
LIKE ‘%d%’ would find any record that has the letter ‘d’ in the value
describe the SQL
percentage (%) symbol
and three example usages
Syntax:
$mysqli -> prepare(query)
Param:
$mysqli - the database object/connection
Query - the sql query we wish to have prepared
what is the syntax and params of the method
prepare()
give 2 points regarding the
tables in a reational database
points include:
a)Each one of these will store data that represents a single entity such as “employee”
b)entities that share a relationship can be linked together using primary keys which can make for more complex data structures
what is the syntax and params of the results object property
num_rows
Syntax for results object property:
$result->num_rows
Param:
$result - the results object we want to find the number of rows it contains
This can be used to set the order of the output
we can specify which column should be used to define the sorting and whether it should be sorted ascending or descending
describe the SQL clause
ORDER BY
code example:
WHERE lastname = 'Doe'
give an example using the SQL WHERE clause that will
return any rows where the column lastname has the value doe
Why shouldn’t we
output the actual error message to the user
we should never perform this action because this information can be used to a malicious user’s advantage. They could gain knowledge about your application that helps them form an attack
Why should we consider
data from a database as untrusted
We should consider this class of data as untrusted because it is possible that the data was not “cleaned” before it was stored.
If malicious JavaScript code was stored in the database and sent to the user, then a JavaScript attack could occur.
Therefore to make this data safe, we must escape any HTML output to the user to prevent this type of attack
this PHP method is used to fetch a row of data from a results object and then return the row as an associative array where
1. the key is the column name
2. and the value is the associated value for the key
this is useful where we would like to retain column name information instead of knowing just the values of the column
describe the PHP method
**Fetch_assoc() **
during this time errors should be outputted to a file in a secure area, the user should be given a message stating that an error has occurred, and the application should be stopped using a function such as die()
During production
what should be done with errors
Using the $database object, execute the SQL query held in the variable $create_sql against the database
code example:
$database->query($create_sql);
write the PHP MySQLi code that will
Connect to a database server and create a database object
code:
$database = mysqli_connect($database_host, $database_user, $database_password);
name 3 activities that are NOT carried out on a database on a day to day basis
these database activities include:
1. creating tables
2. adding tables
3. modifying table structure
name 3 activities that are carried out on a database on a day to day basis
these database activities include:
1. Create
2. Read
3. Update
4. Delete
these day to day activities are also known as CRUD
name 2 types of
data that would be suitable inside a Non-relational database (NoSQL database)
types of data that would be suitable for this include:
1. Document database, key/value storage, graph database
2. Unstructured data or data that has no clear relationships
describe the SQL keywords
AND, OR
these keywords are used to make searches more specific and add further conditions
Example:
WHERE lastname = 'Doe' AND firstname = 'John' WHERE lastname = 'Doe' OR firstname = 'John'
describe the PHP method
fetch_row()
this method can be used to fetch one row from a results object and then returns it as an indexed array.
This can be most effectively used in a loop to extract every row from a results object and look at any particular column of that row
We should treat this data as untrusted because the user is able to send us any data they like, whether it is a GET or a POST request, and not just what we expect.
This means that when data is received by a PHP script and held in its superglobal variables, this data cannot be trusted and should never be used directly. By treating this data as untrusted, we can mitigate against SQL injection attacks
Why should we
treat data from the user as untrusted
what is the syntax and params of the SQL statement
INSERT INTO
Syntax:
INSERT INTO table (v1, v1, v1)
VALUES (v2, v2, v2)
Parameters:
table: specifies the name of the table that you want to insert new data into.
v1: specifies the columns of the table that you want to populate with new data.
v2: specifies the actual data that will be values within the new row.
Example:
~~~
INSERT INTO users (first_name, last_name, email)
VALUES (‘John’, ‘Doe’, ‘john.doe@example.com’)
~~~
describe step 2 of creating prepared statements
Prepare the statement
at this step of creating a prepared statement we use the prepare() method to prepare the stament
what this means is that we are sending it to the database which will then read and prepare it and understand that this is legitimate SQL that will have unexecutable values added later
Example:$stmt = $database->prepare($sql);
This is used to get a list of current tables in the database.
When querying a database using this statement a results object will be returned that has columns and rows.
In this case there is only one column being the name of the table and a row for each table in the database
describe the SQL statement
SHOW TABLES
in 4 steps describe how the following may occur
SQL injection attack
Definition: This occurs when an attacker submits malicious data to a database through an application, causing the database to execute unintended and potentially harmful SQL statements.
Example:
- The user sends an SQL statement to the server instead of the expected data, such as their name.
- The input from the user is combined with an already written SQL statement, such as an INSERT statement, and is placed as a value that will be stored in the database.
- When the statement is executed, the database cannot differentiate between the legitimate SQL and the injected SQL, and goes on to execute the injected SQL.
- The database is now compromised and has been a victim of an SQL injection attack.
Note: Using prepared statements and other security measures can help prevent SQL injection attacks.
syntax of method:
$mysqli -> query(query)
params:
$mysqli - a databse object
Query - Specifies the SQL query string
what is the syntax and params of the method
query()
what is the syntax and params of the PHP method
execute()
syntax:
$stmt->execute();
Param:
$stmt - an already prepared and bound statement.
these database activities include:
1. Create
2. Read
3. Update
4. Delete
these day to day activities are also known as CRUD
name 3 activities that are carried out on a database on a day to day basis
steps include:
1.Write our SQL statement
2.Prepare our SQL statemnt
3.Bound our SQL statemnt
4.Execute the prepared and bound SQL statement
what are the 4 steps to create a
prepared statement
Why should we
treat data from the user as untrusted
We should treat this data as untrusted because the user is able to send us any data they like, whether it is a GET or a POST request, and not just what we expect.
This means that when data is received by a PHP script and held in its superglobal variables, this data cannot be trusted and should never be used directly. By treating this data as untrusted, we can mitigate against SQL injection attacks
what is the syntax, params and return value of the PHP method
bind_param()
Syntax:$stmt->bind_param(types, vars)
Params:
$stmt - an already prepared statement object
Types - A string that contains one or more characters which specify the types for the corresponding bind variables
Vars - the values we wish to replace the placeholders
note:
number of types and vars must match placeholders and types must match the type of vars
Return:
Returnstrueon success orfalseon failure.
Syntax:
CREATETABLEtable_name( column1 datatype options, column2 datatype options, column3 datatype options, .... );
what is the syntax of the SQL statement
CREATE TABLE
wha is the syntax and params of the SQL statement
SELECT
Syntax for SQL statement:
SELECT columns FROM table
Param
Columns - a comma separated list of columns we would like to select
Table - the table we would like to selct columns from
what is the syntax and params of the PHP method
fetch_assoc()
Syntax for PHP method:
$mysqli_result -> fetch_assoc()
Param:
$mysqli_result - the results object we would like to exract rows from
Syntax ommitted:
mysqli_connect(host, username, password)
Param:
@param host Specifies a host name or an IP address
@param username specifies the database username
@param password specifies the database password
Return:
An object representing the connection to the database server
what is the syntax, params and return of the PHP MySQLi function
mysqli_connect()
code example:
$sql = "SHOW TABLES"; $result = $database->query($sql);
using PHP write a string query that will show all the tables in the database and then execute the query on the database
for this type of SQL statement we would expect the database to return a table like structure in which we can access its rows and columns using a script such as PHP
after executing an SQL SELECT statement what type of data could we expect returned from the database
benefits include:
1. it is more efficient (for database resources and structure)
2. it mitigates errors (an update only has to occur in one place for example)
name 2
benefits of data only existing once inside a relational database
generic steps include:
1.Connect to the database server.
2.Select a specific database to use.
3.Construct an SQL instruction (also known as a ‘query’) as a string.
4.Execute the SQL query.
5.If the query is such that data is to be returned, then collect the data into a variable (such as for display in a dynamic web page) and return it to the web page.
6.Close the connection (this happens automatically when PHP execution is completed).
name 6 generic steps of how we
make an intercation with a database
code example:
$database->query($create_sql);
Using the $database object, execute the SQL query held in the variable $create_sql against the database
this property is a property of the mysqli class in PHP, which represents a connection to a database.
It returns the number of rows affected by the last query that was executed on the database connection.
describe the property
Affected_rows
This SQL statement can be used to gather column information from a table.
Information returned will be a table where each row represents data of the column such as its name and data type
describe the SQL statement
SHOW COLUMNS
using PHP
Using the results object $result get each row as an associative array and for each row echo its key and value
code example:
// Iterate through each row in the result set
while ($row = $result->fetch_assoc()) {
// Output the first name, last name, and email of each user
echo '<p>firstname: ' . $row['firstname'] . '</p>';
echo '<p>lastname: ' . $row['lastname'] . '</p>';
echo '<p>email: ' . $row['email'] . '</p>';
}
after executing an SQL SELECT statement what type of data could we expect returned from the database
for this type of SQL statement we would expect the database to return a table like structure in which we can access its rows and columns using a script such as PHP
what is the syntax of the SQL statement
CREATE TABLE
Syntax:
CREATETABLEtable_name( column1 datatype options, column2 datatype options, column3 datatype options, .... );
to achieve this in PHP, we can use the following code:
error_reporting(E_ALL);
ini_set('display_errors', 1);
notes:
1. The error_reporting() function sets which PHP errors are reported. The argument E_ALL will report all errors
2. The ini_set() function is used to set values of PHP’s configuration settings. Passing the argument (‘display_errors’, 1) allows errors to be displayed for this script.”
How do we
report and display all errors in PHP
this PHP MySQLi function is used to create a connection with a database server
describe the PHP MySQLi function
mysqli_connect()
this is deciding what the application should do when an error occurs
What is
error handling
this Is where a user submits data to the database that is an SQL query and is then succsefull in having that query executed by the database.
this can have devastating effects such as extracting, modifying data creating or deleting tables
describe
SQL injection
code example:
echo '<p>row count: ' . $result->num_rows . '</p>';
using PHP Echo a html paragraph of format
“row count: {rows}”
Where {rows} is the number of rows in the results object $result
Syntax:
DROP TABLE table_name
what is the syntax for the SQL statement
DROP TABLE
code example:
$tables = []; // list of names of tables in database
// Read each row as an array indexed by numbers
while ($row = $result->fetch_row()) {
// Put the first item in each row into the tables array
$tables[] = $row[0];
}
Create an array named tables and using the results object $row place the first column of every row in the tables array
outline what prepared statements
1. will
2. will not
protect
these
1.Will
a)Protect data values from SQL injection
2.Will not
a)Protect column names from SQL injection
b)Protect SQL keywords from SQL injection
What is
error handling
this is deciding what the application should do when an error occurs
what is the syntax, params and return of the PHP MySQLi function
mysqli_connect()
Syntax ommitted:
mysqli_connect(host, username, password)
Param:
@param host Specifies a host name or an IP address
@param username specifies the database username
@param password specifies the database password
Return:
An object representing the connection to the database server
Syntax (Object oriented style):
$mysqli -> select_db(name)
Param:
$mysqli - a variable holding a database object
Name - the name of the database we wish to have access to
Return:
TRUE on success. FALSE on failure
what is the syntax, params and return of the PHP MySQLi method
select_db()
syntax:
$stmt->execute();
Param:
$stmt - an already prepared and bound statement.
what is the syntax and params of the PHP method
execute()
code:
$database = mysqli_connect($database_host, $database_user, $database_password);
write the PHP MySQLi code that will
Connect to a database server and create a database object
describe the PHP method
prepare()
this is used so that we can prepare an SQL query for execution. This carries speed and security advantages.
On execution it will return A statement object on success. FALSE on failure
write an example of protecting against SQL injection when using dynamic column names or keywords from the user in an SQL statement
example:
// Check the value of the 'sort' key in the $data array
if ($data['sort'] == 'firstname') {
// If the value is 'firstname', set the $sort variable to 'firstname'
$sort = 'firstname';
}
if ($data['sort'] == 'lastname') {
// If the value is 'lastname', set the $sort variable to 'lastname'
$sort = 'lastname';
}
// Additional code goes here
// Use the $sort and $order variables to dynamically specify the column and order in an SQL ORDER BY clause
ORDER BY $sort $order
points include:
a)Each one of these will store data that represents a single entity such as “employee”
b)entities that share a relationship can be linked together using primary keys which can make for more complex data structures
give 2 points regarding the
tables in a reational database
How do we
turn off all error reporting in PHP
to achieve this in PHP, we can use the following code:
error_reporting(0);
ini_set('display_errors', 0);
notes:
1. The error_reporting() function sets which PHP errors are reported. The argument 0 turns off error reporting
2. The ini_set() function is used to set values of PHP’s configuration settings. Passing the argument (‘display_errors’, 0) disables error reporting for this script.”
describe the PHP method
execute()
this PHP method is used to execute a prepared statement on a database
this is used so that we can bind any variables we have to an already prepared statement
describe the PHP method
bind_param()
we should never perform this action because this information can be used to a malicious user’s advantage. They could gain knowledge about your application that helps them form an attack
Why shouldn’t we
output the actual error message to the user
these database activities include:
1. creating tables
2. adding tables
3. modifying table structure
name 3 activities that are NOT carried out on a database on a day to day basis
Syntax:
$database->affected_rows
Parameters:
$database: the database object that you are querying.
example:
// Execute an INSERT query on the database object
$result = $database->query("INSERT INTO users (first_name, last_name, email) VALUES ('John', 'Doe', 'john.doe@example.com')");
// Check if the query was successful
if ($database->affected_rows > 0) {
// If at least one row was affected, output a success message
echo "New record created successfully";
} else {
// If no rows were affected, output an error message
echo "Error: " . $database->error;
}
what is the syntax and parameters of the property
affected rows
when storing data values in a relational database. that value should only exist once
if it is required in another table then it should be accessed by using primary keys
in general terms of a relational databse (not mitigating attacks… etc)
how should data values be stored
steps/flow of this process includes:
1.create a HTML form that the user can fill in
2.validate form data in a web browser
3.submit data to a web server
4.validate the received data again on a web server
5.add data to a database table
6.read data from a database table
7.present data as a HTML table.
in 7 steps describe the steps/flow of
1. getting data from a user
2. writing/reading from database
3. sending data back to the user
at this step of creating prepared statements Binding variables to the place holders of the prepared statement
example:$stmt->bind_param('sss', $data['firstname'], $data['lastname'], $data['email']);
note:
the variables will most likely be user input such as that from a form
describe step 3 of creating prepared statements
Bound our SQL statement
write the PHP MySQLi code that will
connect to a database that we specify and check whether it was successful or not
describe the following code:
if (!$database->select_db($database_name)) {
code
}
describe the PHP MySQLi function
mysqli_connect()
this PHP MySQLi function is used to create a connection with a database server
describe the SQL clause
WHERE
this is used so that we can be more specific about the results the database will return
what is the syntax and params of the PHP method
fetch_row()
Syntax of PHP method:$mysqli_result -> fetch_row()
Param:
$mysqli_result - the results object that was returned by an sql query
this SQL keyword can be used with the where clause and gives us a less strict rule to match
describe the SQL keyword
LIKE
Create an array named tables and using the results object $row place the first column of every row in the tables array
code example:
$tables = []; // list of names of tables in database
// Read each row as an array indexed by numbers
while ($row = $result->fetch_row()) {
// Put the first item in each row into the tables array
$tables[] = $row[0];
}
this is used so that we can be more specific about the results the database will return
describe the SQL clause
WHERE
what is a
**pitfall of prepared statements **
and what does this ultimately mean
the pitfall of these is that they only protect the column values of a statement and not the keywords or table names
this means that if we want dynamic selection of keywords and table names by the user then we need another method to ensure the data from the user is not malicious
describe the SQL statement
DROP TABLE
This sql statement is used so that we can delete an entire table and with it all of its data
describe step 3 of creating prepared statements
Bound our SQL statement
at this step of creating prepared statements Binding variables to the place holders of the prepared statement
example:$stmt->bind_param('sss', $data['firstname'], $data['lastname'], $data['email']);
note:
the variables will most likely be user input such as that from a form
Syntax for results object property:
$result->num_rows
Param:
$result - the results object we want to find the number of rows it contains
what is the syntax and params of the results object property
num_rows
Definition: This occurs when an attacker submits malicious data to a database through an application, causing the database to execute unintended and potentially harmful SQL statements.
Example:
- The user sends an SQL statement to the server instead of the expected data, such as their name.
- The input from the user is combined with an already written SQL statement, such as an INSERT statement, and is placed as a value that will be stored in the database.
- When the statement is executed, the database cannot differentiate between the legitimate SQL and the injected SQL, and goes on to execute the injected SQL.
- The database is now compromised and has been a victim of an SQL injection attack.
Note: Using prepared statements and other security measures can help prevent SQL injection attacks.
in 4 steps describe how the following may occur
SQL injection attack
using PHP write a string query that will show all the tables in the database and then execute the query on the database
code example:
$sql = "SHOW TABLES"; $result = $database->query($sql);
Syntax:$stmt->bind_param(types, vars)
Params:
$stmt - an already prepared statement object
Types - A string that contains one or more characters which specify the types for the corresponding bind variables
Vars - the values we wish to replace the placeholders
note:
number of types and vars must match placeholders and types must match the type of vars
Return:
Returnstrueon success orfalseon failure.
what is the syntax, params and return value of the PHP method
bind_param()
code example:
ORDER BY lastname DESC
using the SQL clause ORDER BY write the statement that will
**Order the selected data in descending order using the lastname column **
The first step in creating a prepared statement is to write the SQL statement that you want to execute.
Example:
$sql = "INSERT INTO users (first_name, last_name, email) VALUES (?, ?, ?)";
note:
The placeholders ? are used to indicate where data will be inserted into the statement. this data comes from the user and is untrusted
describe step 1 of creating prepared statements
Write our SQL statement
This sql statement is used so that we can delete an entire table and with it all of its data
describe the SQL statement
DROP TABLE
How do we
report and display all errors in PHP
to achieve this in PHP, we can use the following code:
error_reporting(E_ALL);
ini_set('display_errors', 1);
notes:
1. The error_reporting() function sets which PHP errors are reported. The argument E_ALL will report all errors
2. The ini_set() function is used to set values of PHP’s configuration settings. Passing the argument (‘display_errors’, 1) allows errors to be displayed for this script.”
points include:
1.Makes use of SQL
2.Makes use of many tables
3.Suited for large data that is related to each other
give 3 points regarding a
relational databse
this PHP MySQLi function is used to select a database and connect our database object to it
describe the PHP MySQLi method
select_db()
after executing an SQL DELETE statement what type of data could we expect returned from the database
for this type of SQL statement we would expect the database to return an integer representing the number of affected rows
code xample:
WHERE last_name LIKE '%d%'
using the SQL WHERE clause and the LIKE keyword write the statement that will
Will return any rows where the last_name column holds a value d anywhere in the entry
what is a
prepared statement
these are formed using PHP methods and is a method that allows us to mitigate SQL injection attacks.
What data should
always be considered untrustworthy
This class of data is data from the user and data that comes out of the database
describe the SQL statement
SHOW TABLES
This is used to get a list of current tables in the database.
When querying a database using this statement a results object will be returned that has columns and rows.
In this case there is only one column being the name of the table and a row for each table in the database
this results object property can be used to return the number of rows that are present within a results object
describe the fllowing property of a results object
num_rows
describe the SQL statement
INSERT INTO
this SQL statement is used to insert a new row of data into a given table
what is a
result object
this is data that is returned from a database in the form of a table with columns and rows
using methods such as fetch_row() we can collect the data we would like and hold it in arrays or variables
this action should be performed on any user data that will dynamically change:
1. Database names
2. Table names
3. Column names
4. SQL keywords
What values should we set in a script and use comparisons on the user’s data and our own values to protect against SQL injection attacks
during this time it may be easier to output errors to the web browser
**During development **
where should errors be outputted?
describe step 1 of creating prepared statements
Write our SQL statement
The first step in creating a prepared statement is to write the SQL statement that you want to execute.
Example:
$sql = "INSERT INTO users (first_name, last_name, email) VALUES (?, ?, ?)";
note:
The placeholders ? are used to indicate where data will be inserted into the statement. this data comes from the user and is untrusted
describe the PHP method
**Fetch_assoc() **
this PHP method is used to fetch a row of data from a results object and then return the row as an associative array where
1. the key is the column name
2. and the value is the associated value for the key
this is useful where we would like to retain column name information instead of knowing just the values of the column
give 3 points regarding a
relational databse
points include:
1.Makes use of SQL
2.Makes use of many tables
3.Suited for large data that is related to each other
name 6 generic steps of how we
make an intercation with a database
generic steps include:
1.Connect to the database server.
2.Select a specific database to use.
3.Construct an SQL instruction (also known as a ‘query’) as a string.
4.Execute the SQL query.
5.If the query is such that data is to be returned, then collect the data into a variable (such as for display in a dynamic web page) and return it to the web page.
6.Close the connection (this happens automatically when PHP execution is completed).
code example:
// Iterate through each row in the result set
while ($row = $result->fetch_assoc()) {
// Output the first name, last name, and email of each user
echo '<p>firstname: ' . $row['firstname'] . '</p>';
echo '<p>lastname: ' . $row['lastname'] . '</p>';
echo '<p>email: ' . $row['email'] . '</p>';
}
using PHP
Using the results object $result get each row as an associative array and for each row echo its key and value
During production
what should be done with errors
during this time errors should be outputted to a file in a secure area, the user should be given a message stating that an error has occurred, and the application should be stopped using a function such as die()
describe the SQL
percentage (%) symbol
and three example usages
this is the wild card symbol in SQL and is used with the LIKE keyword
LIKE ‘d%’ - would find any record that starts with the letter ‘d’
LIKE ‘%d’ - would find any record that ends with the letter ‘d’.
LIKE ‘%d%’ would find any record that has the letter ‘d’ in the value
this SQL statement is used to insert a new row of data into a given table
describe the SQL statement
INSERT INTO
types of data that would be suitable for this include:
1. Document database, key/value storage, graph database
2. Unstructured data or data that has no clear relationships
name 2 types of
data that would be suitable inside a Non-relational database (NoSQL database)
at this step we have a prepared and bound SQL statement and we are ready to execute it on the database.
Example:$stmt->execute();
describe step 4 of creating prepared statements
Execute the prepared and bound statement
within php we use the “->” operator
this differs from other languages that mostly use dot notation
in PHP how do we call the methods and properties of an object
what is the syntax for the SQL statement
DROP TABLE
Syntax:
DROP TABLE table_name
describe
SQL injection
this Is where a user submits data to the database that is an SQL query and is then succsefull in having that query executed by the database.
this can have devastating effects such as extracting, modifying data creating or deleting tables
name 2
**approcahes to creating a databse **
approaches include:
1.Relational database
2.Non-relational database (NoSQL database)
these
1.Will
a)Protect data values from SQL injection
2.Will not
a)Protect column names from SQL injection
b)Protect SQL keywords from SQL injection
outline what prepared statements
1. will
2. will not
protect
what is the syntax and parameters of the property
affected rows
Syntax:
$database->affected_rows
Parameters:
$database: the database object that you are querying.
example:
// Execute an INSERT query on the database object
$result = $database->query("INSERT INTO users (first_name, last_name, email) VALUES ('John', 'Doe', 'john.doe@example.com')");
// Check if the query was successful
if ($database->affected_rows > 0) {
// If at least one row was affected, output a success message
echo "New record created successfully";
} else {
// If no rows were affected, output an error message
echo "Error: " . $database->error;
}
This class of data is data from the user and data that comes out of the database
What data should
always be considered untrustworthy
in general terms of a relational databse (not mitigating attacks… etc)
how should data values be stored
when storing data values in a relational database. that value should only exist once
if it is required in another table then it should be accessed by using primary keys
syntax for SQL statement:SHOW COLUMNS FROM a_table
what is the syntax of the SQL statement
SHOW COLUMNS
Syntax of PHP method:$mysqli_result -> fetch_row()
Param:
$mysqli_result - the results object that was returned by an sql query
what is the syntax and params of the PHP method
fetch_row()
at this step of creating a prepared statement we use the prepare() method to prepare the stament
what this means is that we are sending it to the database which will then read and prepare it and understand that this is legitimate SQL that will have unexecutable values added later
Example:$stmt = $database->prepare($sql);
describe step 2 of creating prepared statements
Prepare the statement
in PHP how do we call the methods and properties of an object
within php we use the “->” operator
this differs from other languages that mostly use dot notation
We should NEVER use form data directly to populate column names or SQL keywords that we want to be dynamic. INSTEAD, we should use a comparison in the code between the form data and our expected values. If there is a match, we can explicitly set a variable ourselves that will be used to dynamically fill the SQL statement. This way, we can protect against SQL injection by ignoring any value that is not expected or proper
How can we
protect against SQL injection when using dynamic column names or keywords that are recieved from the user in an SQL statement?
using PHP Echo a html paragraph of format
“row count: {rows}”
Where {rows} is the number of rows in the results object $result
code example:
echo '<p>row count: ' . $result->num_rows . '</p>';
this method can be used to fetch one row from a results object and then returns it as an indexed array.
This can be most effectively used in a loop to extract every row from a results object and look at any particular column of that row
describe the PHP method
fetch_row()
Syntax for PHP method:
$mysqli_result -> fetch_assoc()
Param:
$mysqli_result - the results object we would like to exract rows from
what is the syntax and params of the PHP method
fetch_assoc()
what is the syntax, params and return of the PHP MySQLi method
select_db()
Syntax (Object oriented style):
$mysqli -> select_db(name)
Param:
$mysqli - a variable holding a database object
Name - the name of the database we wish to have access to
Return:
TRUE on success. FALSE on failure
in 7 steps describe the steps/flow of
1. getting data from a user
2. writing/reading from database
3. sending data back to the user
steps/flow of this process includes:
1.create a HTML form that the user can fill in
2.validate form data in a web browser
3.submit data to a web server
4.validate the received data again on a web server
5.add data to a database table
6.read data from a database table
7.present data as a HTML table.
what is the syntax and params of the method
query()
syntax of method:
$mysqli -> query(query)
params:
$mysqli - a databse object
Query - Specifies the SQL query string
describe the SQL keyword
LIKE
this SQL keyword can be used with the where clause and gives us a less strict rule to match
these are formed using PHP methods and is a method that allows us to mitigate SQL injection attacks.
what is a
prepared statement
these keywords are used to make searches more specific and add further conditions
Example:
WHERE lastname = 'Doe' AND firstname = 'John' WHERE lastname = 'Doe' OR firstname = 'John'
describe the SQL keywords
AND, OR
give an example using the SQL WHERE clause that will
return any rows where the column lastname has the value doe
code example:
WHERE lastname = 'Doe'
describe the property
Affected_rows
this property is a property of the mysqli class in PHP, which represents a connection to a database.
It returns the number of rows affected by the last query that was executed on the database connection.
using the SQL clause ORDER BY write the statement that will
**Order the selected data in descending order using the lastname column **
code example:
ORDER BY lastname DESC
what are the 4 steps to create a
prepared statement
steps include:
1.Write our SQL statement
2.Prepare our SQL statemnt
3.Bound our SQL statemnt
4.Execute the prepared and bound SQL statement
this PHP method is used to execute a prepared statement on a database
describe the PHP method
execute()
this is data that is returned from a database in the form of a table with columns and rows
using methods such as fetch_row() we can collect the data we would like and hold it in arrays or variables
what is a
result object
approaches include:
1.Relational database
2.Non-relational database (NoSQL database)
name 2
**approcahes to creating a databse **
describe the following code:
if (!$database->select_db($database_name)) {
code
}
write the PHP MySQLi code that will
connect to a database that we specify and check whether it was successful or not
