Part 5 - Server side Using databases with PHP Flashcards
(160 cards)
1
Q
describe the fllowing property of a results object
num_rows
A
this results object property can be used to return the number of rows that are present within a results object
2
Q
describe the PHP MySQLi method
select_db()
A
this PHP MySQLi function is used to select a database and connect our database object to it
3
Q
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
A
what is a
**pitfall of prepared statements **
and what does this ultimately mean
4
Q
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')
A
what is the syntax and params of the SQL statement
INSERT INTO
5
Q
describe the method
query()
A
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
6
Q
what is the syntax and params of the method
prepare()
A
Syntax:
$mysqli -> prepare(query)
Param:
$mysqli - the database object/connection
Query - the sql query we wish to have prepared
7
Q
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
A
Why should we consider
data from a database as untrusted
8
Q
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.”
A
How do we
turn off all error reporting in PHP
9
Q
name 2
benefits of data only existing once inside a relational database
A
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)
10
Q
describe the SQL statement
SHOW COLUMNS
A
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
11
Q
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
A
write an example of protecting against SQL injection when using dynamic column names or keywords from the user in an SQL statement
12
Q
**During development **
where should errors be outputted?
A
during this time it may be easier to output errors to the web browser
13
Q
describe the SQL clause
ORDER BY
A
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
14
Q
for this type of SQL statement we would expect the database to return an integer representing the number of affected rows
A
after executing an SQL DELETE statement what type of data could we expect returned from the database
15
Q
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
A
code xample:
WHERE last_name LIKE '%d%'
16
Q
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
A
wha is the syntax and params of the SQL statement
SELECT
17
Q
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
A
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
18
Q
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
A
describe the method
query()
19
Q
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
A
describe the PHP method
prepare()
20
Q
describe the PHP method
bind_param()
A
this is used so that we can bind any variables we have to an already prepared statement
21
Q
How can we
protect against SQL injection when using dynamic column names or keywords that are recieved from the user in an SQL statement?
A
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
22
Q
what is the syntax of the SQL statement
SHOW COLUMNS
A
syntax for SQL statement:SHOW COLUMNS FROM a_table
23
Q
describe step 4 of creating prepared statements
Execute the prepared and bound statement
A
at this step we have a prepared and bound SQL statement and we are ready to execute it on the database.
Example:$stmt->execute();
24
Q
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
A
describe the SQL
percentage (%) symbol
and three example usages
25
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()**
26
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
27
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
28
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**
29
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**
30
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
31
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
32
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() **
33
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
34
Using the `$database` object, execute the SQL query held in the variable `$create_sql` against the database
code example:
`$database->query($create_sql);`
35
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);`
36
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
37
name 3 activities that are carried out on a database on a day to day basis
these database activities include:
1. **C**reate
2. **R**ead
3. **U**pdate
4. **D**elete
these day to day activities are also known as **CRUD**
38
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
39
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'
```
40
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
41
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**
42
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')
```
43
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);`
44
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**
45
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:
1. The user sends an SQL statement to the server instead of the expected data, such as their name.
2. 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.
3. 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.
4. 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.
46
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()**
47
what is the syntax and params of the PHP method
**execute()**
syntax:
$stmt->execute();
Param:
**$stmt** - an already prepared and bound statement.
48
these database activities include:
1. **C**reate
2. **R**ead
3. **U**pdate
4. **D**elete
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
49
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**
50
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
51
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:
Returns true on success or false on failure.
52
Syntax:
```
CREATE TABLE table_name (
column1 datatype options,
column2 datatype options,
column3 datatype options,
....
);
```
what is the syntax of the SQL statement
**CREATE TABLE**
53
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
54
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
55
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()**
56
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
57
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
58
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**
59
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
60
code example:
`$database->query($create_sql);`
Using the `$database` object, execute the SQL query held in the variable `$create_sql` against the database
61
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**
62
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**
63
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 '
firstname: ' . $row['firstname'] . '
'; echo 'lastname: ' . $row['lastname'] . '
'; echo 'email: ' . $row['email'] . '
'; } ```
64
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
65
what is the syntax of the SQL statement
**CREATE TABLE**
Syntax:
```
CREATE TABLE table_name (
column1 datatype options,
column2 datatype options,
column3 datatype options,
....
);
```
66
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**
67
this PHP MySQLi function is used to create a connection with a database server
describe the PHP MySQLi function
**mysqli_connect()**
68
this is deciding what the application should do when an error occurs
What is
**error handling**
69
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**
70
code example:
`echo '
row count: ' . $result->num_rows . '
';`
using PHP Echo a html paragraph of format
**“row count: {rows}”**
Where {rows} is the number of rows in the results object $result
71
Syntax:
`DROP TABLE table_name`
what is the syntax for the SQL statement
**DROP TABLE**
72
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
73
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
74
What is
**error handling**
this is deciding what the application should do when an error occurs
75
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
76
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()**
77
syntax:
$stmt->execute();
Param:
**$stmt** - an already prepared and bound statement.
what is the syntax and params of the PHP method
**execute()**
78
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**
79
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
80
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
```
81
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**
82
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."
83
describe the PHP method
**execute()**
this PHP method is used to execute a prepared statement on a database
84
this is used so that we can bind any variables we have to an already prepared statement
describe the PHP method
**bind_param()**
85
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**
86
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
87
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**
88
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**
89
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
90
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**
91
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
}
```
92
describe the PHP MySQLi function
**mysqli_connect()**
this PHP MySQLi function is used to create a connection with a database server
93
describe the SQL clause
**WHERE**
this is used so that we can be more specific about the results the database will return
94
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
95
this SQL keyword can be used with the where clause and gives us a less strict rule to match
describe the SQL keyword
**LIKE**
96
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];
}
```
97
this is used so that we can be more specific about the results the database will return
describe the SQL clause
**WHERE**
98
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
99
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
100
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
101
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**
102
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:
1. The user sends an SQL statement to the server instead of the expected data, such as their name.
2. 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.
3. 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.
4. 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**
103
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);
```
104
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:
Returns true on success or false on failure.
what is the syntax, params and return value of the PHP method
**bind_param()**
105
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 **
106
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**
107
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**
108
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."
109
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**
110
this PHP MySQLi function is used to select a database and connect our database object to it
describe the PHP MySQLi method
**select_db()**
111
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
112
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**
113
what is a
**prepared statement**
these are formed using PHP methods and is a method that allows us to mitigate SQL injection attacks.
114
What data should
**always be considered untrustworthy**
This class of data is data from the user and data that comes out of the database
115
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
116
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**
117
describe the SQL statement
**INSERT INTO**
this SQL statement is used to insert a new row of data into a given table
118
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
119
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
120
during this time it may be easier to output errors to the web browser
**During development **
where should errors be outputted?
121
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
122
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
123
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
124
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).
125
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 '
firstname: ' . $row['firstname'] . '
'; echo 'lastname: ' . $row['lastname'] . '
'; echo 'email: ' . $row['email'] . '
'; } ```
using PHP
Using the results object $result get each row as an associative array and for each row echo its key and value
126
**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()
127
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
128
this SQL statement is used to insert a new row of data into a given table
describe the SQL statement
**INSERT INTO**
129
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)**
130
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**
131
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
132
what is the syntax for the SQL statement
**DROP TABLE**
Syntax:
`DROP TABLE table_name`
133
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
134
name 2
**approcahes to creating a databse **
approaches include:
1.Relational database
2.Non-relational database (NoSQL database)
135
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
136
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;
}
```
137
This class of data is data from the user and data that comes out of the database
What data should
**always be considered untrustworthy**
138
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
139
syntax for SQL statement:
`SHOW COLUMNS FROM a_table`
what is the syntax of the SQL statement
**SHOW COLUMNS**
140
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()**
141
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**
142
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
143
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?**
144
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 '
row count: ' . $result->num_rows . '
';`
145
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()**
146
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()**
147
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
148
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.
149
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
150
describe the SQL keyword
**LIKE**
this SQL keyword can be used with the where clause and gives us a less strict rule to match
151
these are formed using PHP methods and is a method that allows us to mitigate SQL injection attacks.
what is a
**prepared statement**
152
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**
153
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'`
154
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.
155
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`
156
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
157
this PHP method is used to execute a prepared statement on a database
describe the PHP method
**execute()**
158
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**
159
approaches include:
1.Relational database
2.Non-relational database (NoSQL database)
name 2
**approcahes to creating a databse **
160
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**
