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
