IT 202 Practice Midterm

SQL: Given a table named students [name, age, ssn] with columns name, age, ssn of type varchar, int and varchar, respectively, write a simple SQL statement that inserts a row with values Mary for name, 33 for age and 888 for ssn.

insert into R values ('Mary', 33, '888')

DB: What is the SQL statement that empties the contents of a table named AA?

truncate table AA

DB: Could the attribute fullname in the users table be indexed (assume it is of type varchar.) Different data types can be used such as varchar and datetime.

yes. The fullname column make sense here just like they do for a telephone directory.

DB: The maximum length for a valid email address is how many characters?

255 This is independent of the varchar max length of 255 characters allowed in MySQL.

We defined an enhanced version of get ( ) that tests whether its argument is undefined or empty. What condition did we use to make that test? State the condition as correct PHP code;

! isset ( $_GET [$fieldname] ) || $_GET [$fieldname] == "" )

PHP: If an HTML form's text field is named "ucid" how does the standard built-in PHP associative array $_GET (a) access the data entered on the form after the form's inputs have been submitted to the web server and b) store it in $UCID using an assignment statement.

$UCID = $_GET ["ucid"];

PHP: Write a PHP statement that uses the appropriate mysqli_ function to transform a form input stored in $X to prevent an SQL injection. You can assume the database is already connected to and pointed to by $db.

$X = mysqli_real_escape_string ($db, $X);

PHP: We used the programmer-defined get ( ) function in Assignment 01 to acquire input. If a query string is ?ucid=joe2&pass=999&amount=10.50, then what statement accesses the amount input and stores its value in $amount?

$amount = get ("amount");

26. PHP: Suppose the results retrieved by executing a select SQL statement are returned and stored in $t. Write a PHP statement that sets $count equal to the number of rows retrieved.

$count = mysqli_num_rows ($t);

PHP: Assume the data just retrieved is from the table transactions which has columns ucid, amount, mail, etc. Suppose a row of these retrieved results is stored in $r. Write a statement that stores the retrieved value from the mail column of $r in $mail?

$mail = $r ["mail"];

PHP: What PHP expression can be used to extract the 1st row of a resultSet $t that has been retrieved from MySQL and then store that row in an array $r?

$r = mysqli_fetch_array ($t, MYSQLI_ASSOC)

Write a PHP statement that executes an SQL statement in $s and reports the actual MySQL error message (and terminates the PHP script) if the SQL statement has an SQL error.

($t = mysqli_query ($db, $s ) ) or die ( mysqli_error ( $db ) ) ;

(1) Write a PHP statement that incorporates into a PHP script the assignment statements in the file account.php. (2)Will the script terminate immediately after the include is executed if the file account.php does not exist?

(1) include ("account.php"); (2) The script does not immediately terminate if the included file is not found. However if we use the PHP require statement instead of the include statement the script would terminate immediately. On the other hand even for the include statement, if the file were not found the script would terminate because of subsequent errors that will trigger in the database connection code.

PHP: Circle which takes longer to execute: (1) A PHP statement that triggers an SQL request like executing a query or (2) An include statement that includes the database credentials for a database in a PHP script?

(1). For a short explanation: mysqli_ incurs a transmission delay that include does not. The function mysqli_query accesses a remote DB server, and waits for a response. The include statement is just copying a file from the local memory on the web server so there is no transmission delay as there is when contacting the remote database. The transmission delay is orders of magnitude greater than the time to access the local memory; such as, on the order of seconds for transmission, versus the order of microseconds or less for the memory access.

23. PHP: What file is returned if the start form tag (in an HTML page, named form.html, which has been downloaded via an https request from a web server) is: <form "mycode.php" > a. PHP syntax error b. form.html c. file not found error d. None of these e. mycode.php

(b) If there is no action attribute in the <form> tag (as is the case here) then the current file or page is reloaded when the form is submitted. The best way to verify what happens is to test this live on an example with the above form tag. For example test the posted test1.html example.

HTML: Suppose an HTML form on has only the following input element: <input type = text value="Joe" >Enter Name What is transmitted to the PHP script requested when the form is submitted? a. Joe b. Null c. Enter Name d. None of these

(d) No name=value pair is transmitted in the query string since the element has no name. Test this by making or modifying an existing form you have and look at the URL it generates. See example test0.html.

If the students table has 20 rows and ssn is a primary key and name is not indexed, then how many rows would the database have to search to answer the query in (19)?

20 rows (c) All 20 rows since the name could appear in none, any or all of them. You don't know so they all must be searched.

Statement: We have used the so-called HTTP "GET method" for all the forms we have considered. This refers to how the form data is transmitted to the server. Usually the alternative POST method is recommended when an update or database insert is being done, as in Assignment 01. But GET has certain advantages which is why I used it.

1. The GET method makes the browser trigger an easily seen and understood query-string in the URL while the POST method does not so GET is handier to use during development. 2. The GET query string also makes it easy to see if a submitted input is empty or incorrectly named which helps detect errors like inconsistencies between names on a form and how PHP accesses these quantities. 3. GET lets us easily alter the inputs without even using the form that generates the URL query string - which is especially useful for testing during development. One can just edit the query string and refresh the browser to submit a different request 4. While GET lets us just refresh the browser to repeat the requested form action, a POST asks the browser user to confirm such a resubmit. This is safer when changes are being made to the database. It's harder to inadvertently request a script execution twice since the browser requires you to confirm the refresh, unlike for a GET. 5. POST can transmit more data like for file uploads, and even a broader set of characters than a GET. 6. Note that the terminology GET for the HTTP "method" is related to our use of the PHP array $_GET.

PHP: Do the following capture the value in an HTML field named "UCID" (true or false): 1. $ucid = $_GET [ "ucid" ] ; 2. $x = $_GET ["ucid"] ; 3. $UCID = $_GET [ "UCID" ] ; 4. $ucid = $_GET [ " ucid " ];

1. True - the spaces around the argument [ "ucid" ] are outside the string and so are cosmetic 2. True - though the result is stored in a differently named PHP variable $x 3. False - the argument of $_GET[ "UCID" ] is case-sensitive so $ucid would end up as empty 4. False - the argument of $_GET must match HTML name without any extra spaces like " ucid ".

Suppose $t is the resultSet retrieved/selected from a database by executing an SQL select statement and that $t has 5 rows of data. How often is the condition in the following while loop executed: while ($r = mysqli_fetch_array ($t)){ } ;

6 (six) More generally, the condition is always executed at least once even if no rows are retrieved, twice if one row is retrieved, etc. The last time the condition executes it fails or is set to false (since by that time the resultSet $t has been emptied) so the loop is then just exited.

HTML: How is an HTML page 'declared' as HTML5 ?

<!DOCTYPE html> at top of page. Without this declaration the browser defaults to an earlier version of HTML and may not handle newer HTML5 features like the required, placeholder and autocomplete attributes.

HTML: Write an HTML text field element named "X" that places a hint such as "Enter X" in the text field.

<input type =text name="X" placeholder = "Enter name" >

HTML: How do you declare the character representation used for an HTML5 page.

<meta charset = "UTF-8" >. UTF-8 is the most widely used binary representation for characters.

CSS/HTML: Write a CSS rule, including the style element in which the rule is stated, that makes the background of a <span> element with id="message" lightgray when the page is loaded. Use the RGB notation for a light gray.

<style> #message { background-color: #cccccc ; } </style> Note that #ffffff is the hexadecimal notation for white and is the hexadecimal notation #000000 is black. IN RGB six hex digits define the amount of Red Green and Blue in a color. Thus #000000 means there are no red, green or blue components so the color is black. If #FFFFFF is used it means each of the three color components are turned and so the color is white. Intermediate values make various shades of gray. You can test these for example in the MS Word color widget for text (pick the 'custom' tab).

HTML: A form on an HTML page has input element: <input type = text name="A" value="B">Enter Name. What name=value pair is transmitted for this element to the form's requested PHP script when this form is submitted?

?A=B Test this by making or modifying an existing form you already have and look at the URL it generates when submitted. Or see example test20.html.

HTML: Suppose a form has two input elements named "A" and "C" respectively. Suppose "A" is a text field and "C" is a checkbox. Suppose the data entered into "A" on the form is "Moe". Suppose "C" is not checked. Suppose the form tag indicates the target script is "form.php". What query string is generated by the browser when the form is submitted?

?A=Moe Nothing is transmitted in the generated URL if checkbox "C" is unchecked. If it were checked C=on would be transmitted.

HTML: Suppose a form has two input elements of type text named "A" and "B" respectively and assume that the data entered into them is "Moe" for A and "9 9" for B where there is a space between the 9's. Suppose the 6 form tag correctly indicates the target action script is "form.php" and uses the default http GET method. What query string is generated by the browser when the form is submitted?

?A=Moe&B=9+9 The space in 9 9 is automatically converted to the + in the query string by the browser. This is an example of what is called urlencoding. It is done for certain special characters like the space, an ampersand, a question- mark, etc that are reserved for special roles in the url.

PHP: Suppose the following code is executed (after database connection, etc) for a syntactically valid SQL select statement (for example, for retrieving rows from users table of Assignment 01). ($t = mysqli_query ($db, $s)) or die (mysqli_error ($db) ) ; echo "continue"; 9 If the select retrieves zero rows for users is the "continue" above executed or do we get a mysql error statement because we failed to retrieve any rows.

Answer: The "continue" is executed because there is no SQL error. Getting zero rows returned is not an error condition so mysqli_error ($db) is not executed.

CLI: Suppose you are using the NJIT AFS Linux command prompt. Write the Linux command that connects you to your mysql database.

Answer: refer to Prototype 01 for the command and its format.

PHP: Circle exactly where the syntax error(s) in the following meandeviation error display: 24: $A = $_GET ["A"]; 25: print <br>"Hello $A ";

Answer: the <br> tag should be inside the quotes because the print statement expects to transmit a string.

DB: If two columns ucid and account are declared primary in a database table named accounts as used in Assignment01 then what kind of key results?

Composite

DB: Given the table students [name, age, ssn] and assume ssn is a primary key. What error occurs if the same (correct) SQL insert statement as above was executed twice in a row?

Duplicate key error for the 2nd attempted insert of the same primary key value.

PHP: An SQL statement stored in a PHP string (like select * from users") cannot cause an SQL error as long as its syntax is correct.

False The SQL may have a run-time error such as the error that occurs when a referred to database table no longer exists or a misspelt column name is used for a database table.

PHP: When a syntax error is detected in meandeviation.com an arrow in the displayed listing points to some line. The syntax error will be on that line or the preceding line.

False. The error may be many lines before the line pointed to. The compiler merely did not recognize the error until that point.

HTML: Write an HTML menu with two choices. The 1st row has the text Choose and an empty string value; the 2nd row has the visible text Deposit and value "D", the 3 rd row has visible text Withdraw and value "W".

HTML for menu is: <select name="choice> <option value = ''" > Choose </option> <option value = 'd' > Display </option> <option value = 't' > Transact </option> </select> The PHP to access the value selected in a menu when its form is submitted is: $choice = $_GET [ "choice" ] ; That is the PHP accessed the value in the same name-based way as any other form input element.

Web Server: What does an HTTP 500 error indicate?

In our applications this typically indicates a fatal (PHP syntax) error occurred on the requested page. It is a catch-all error that indicates the web server from which the page was requested had a problem satisfying the request.

PHP: If NOW ( ) is used in a PHP string defining an SQL statement is the NOW ( ) function evaluated in the PHP script before transmission to the DB server or is it evaluated on the DB server?

NOW ( ) is evaluated in the database. Incidentally there is no PHP NOW function

What did we define as the get ( ) function's return value [in the enhanced version of the get ( ) function] in the case that the input is either undefined or empty?

NULL

In the users table for Assignment 01 ucid was primary and mail was unique. If we had made mail primary (along with ucid) would mail still have to be unique?

No. Remark: If the pair UCID and mail are combined in a composite key then neither mail nor ucid in users would be constrained by the database to be unique.

PHP: We have included the account.php file in any script that accesses the database. We executed that include statement before executing the database connection code. Can the connection code be executed first, before the include statement?

No. The database connection code uses the value of variables like $hostname, etc that are defined in the account.php file. If they are not already defined when the connection code is executed then they will be taken as empty and the attempted connection will fail.

DB: In phpMyAdmin, can you easily edit a database table that has no primary key?

No. The edit icons are only available when there is a primary key for the table. You would have to use, for example an update SQL statement that you invent to do the editing you want.

PHP: Assume a database table A has only two columns, of type varchar and datetime respectively. Assume the database has already been connected to. Is the following correct PHP: $date = NOW ( ); $s = "insert into X values ('dog', '$date') " ;

No. There is no PHP NOW function.

DB: Does an indexed key identify table rows uniquely?

No. Think of a telephone directory for a town where there are many duplicate names. The sorted order of the names allows the rapid retrieval of phone numbers for those names. This corresponds to an index to a table that provides that kind of fast access for a sought name -- even though the indexed names are not unique.

Web Server: Suppose there is a file named my.html on my desktop. When I open it in a browser the address bar has the following URL: file:///C:/Users/mchugh/Desktop/Weeks/WEEK04/my.html and shows the rendered 11 HTML form. You would see something similar on your own (windows) desktop. The form has a submit button and action = "my.php". Assume my.html file is a valid complete HTML form and does not try to restrict the submission (such as because of a JavaScript test). Assume the file my.php exists in the same directory on my desktop as my.html and contains no syntax or run-time errors, just correct PHP code. What happens when I click the submit button on the form?

Part of the my.php's code is displayed in the browser. It is not executed. The code displayed depends on the html tags embedded in the PHP page. Test this yourself to see what happens. The point of this question is that the files and programs we have developed operate in a 3-tier web server environment, of which the browser desktop is only one component. The browser must interacting with a remote web server that understands PHP to execute the PHP code file requested.

DB: The timestamp column in transactions (for Assignment 01) is of type datetime. What function did we use to insert a datetime into the table?

The SQL NOW ( ) function.

If the value of $user is O'Malley and the function above is applied what is the result?

The data gets changed to O\'Malley. Test this in your own code to see what happens. When printed to browser that's what you see. If it's inserted in the database, it ends up there as O'Malley because the backslash \ tells the database system that the single quote after the slash is a to be handled as data (not a syntax character). This prevents the database from inadvertently using the single quote as part of the SQL syntax (which can allow an SQL injection attack).

PHP: What kind of error (if any) does the following SQL statement cause if it is submitted to a database server for execution (by a PHP script connected to the data base server with connection pointer $db)? $ucid = "joe"; $s = "select * from users where ucid = $ucid " ; mysqli_query($db, $s) ;

The reference to $user in $s requires single quotes like in '$ucid'. Without that the database will report an SQL unknown column error. Try it in a PHP script to see what happens.

PHP: How many PHP errors are there in the following PHP statement: prin "hello br>

There are three PHP errors: the command print is misspelled, the string lacks the second closing quote, and the statement lacks a final semi-colon. The correct version is: print "hello br>"; The missing angular bracket < is an HTML but not a PHP error.

Web Server: What does an HTTP 404 error indicate?

This error occurs when a browser requests a non-existent page from a web site. For some 'comic' examples by site-administrators see creative-404s -- a web server could send one of these if you try to access a non-existent page on the web site.

PHP: Write the PHP code for the authenticate function used in Assignment 01 but change the names as follows: a. Assume table named cred with the credentials is and the ucid and password columns are named u and p. b. Assume parameters for the function are $u, $p and $db respectively. c. Use $db as an argument of the function. d. Assume all the inputs have already been acquired and sanitized. e. Define the appropriate SQL select statement with correct SQL and PHP syntax to check the credentials f. Echo the select statement string properly including appropriate descriptive text and <br>'s g. Execute the statement using the correct mysqli_ function (using the procedural mysqli_ version) h. Return true or false depending on whether the credentials were valid or not.

This is like the authenticate function used in Assignment 01 with changes to the names of the database table, arguments, and table column names to help ensure you understand the code

SQL: Write an SQL statement that returns only the single most recent transaction for 'joe1' from table transactions based on the timestamp column in transactions.

This requires some simple research because it uses SQL keyword terminology like the Order By and Limit features we only briefly considered but which were required for Assignment 01. Be sure to test your result in phpMyAdmin to confirm it behaves as expected. Paste the SQL statement in the SQL tab in phpMyAdmin to see if it gives the expected answer.

PHP: Write the PHP code for the function display ($ucid, $account, $box $number, &$results, $db) used in Assignment 01. But only display the transactions output. It must of course test the $box variable that controls what transactions are displayed. Assume the database is already connected to with a connection $db and that the data input has already been obtained, including $box.

This was part of Assignment 01 Remember to: a. Assume the inputs have already been acquired and sanitized. So don't repeat that here. b. Assume the ucid and pass have already been authenticated. So don't repeat that here. c. Assume $amount is a positive decimal number. d. Define the appropriate SQL select statements to retrieve the transactions - this depends on $box. e. Echo the SQL properly including some descriptive text that labels the output and <br>'s. f. Execute the SQL using the correct mysqli_ function (procedural version) and also the fallback call to mysqli_error. Get all the arguments right. g. Capture the echoed outputs in $results

Web Server: What specifically happens if form.html is not located under IT202 in the request https://web.njit.edu/~joe123/IT202/form.html?

This will also give an HTTP 404 File Not Found error because form.html is not where the url said it was

Web Server: Assume form.html is located under IT202 directory as in the above question. Notice that there is no explicit reference to the public_html directory in https://web.njit.edu/~joe123/IT202/form.html. Consider the following url which does have an explicit reference to public_html: https://web.njit.edu/~joe123/public_html/IT202/form.html. What happens when this URL request is made?

This will give an HTTP 404 File Not Found error. The reason is that the web server will look for a directory named public_html/IT202 lying under the default public_html directory --- that second public_html directory does not exist so the file is not found.

PHP: The error_reporting code in the beginning of your PHP script helps detect run-time errors not detected by syntax checkers like meandeviation.com. True or False ?

True Meandeviation detects syntax errors that cause the compilation process to stop. The program never gets a chance to execute if there are such errors. You will get either a white screen or an HTTP 500 error page depending on the browser. On the other hand, the error reporting mechanism we use in

51. PHP: The error_reporting code in the beginning of your PHP script helps detects run-time errors like undefined inputs.

True These are reported as non-fatal warnings. The corresponding inputs are set to empty strings.

HTML: What is the query string part in previous question if the password is 8 8 with two spaces between the 8's?

UCID=Boe&pass=8++8&amnt=&mail=on

What is the SQL statement that removes a table AA from the database?

drop table AA

PHP: The get ($fieldname) ) function defined for Assignment 01 echoes its sanitized and trimmed input and embeds its value in a message labelled with the name of the input. What line of code in get ( ) echoes the value (transformed by mysqli_real_escape_string) and reports its name? Assume the transformed value is stored in $v

echo "<br><br>The value of $fieldname is $v." ;

What MySQL data type should be used to restrict the values of a database table column to a particular list of values?

enum like for type in the transactions table.

PHP: What is the declaration for the display ( ) function used in Assignment 01? Assume that we use a version of display that returns the echoed output as a string (rather than as a reference argument.)

function display ($ucid, $account, $box, $number, $db) { } The variable $db is required to allow the function to access the mysqli_ functions it uses. Alternatively we can declare $db as global inside display: global $db;

HTML: Suppose the input text fields on a form are named: "ucid", "pass", "amount", and a checkbox named "mail" and the form's action is "my.php". Suppose the form is downloaded from the directory 202 lying under the public_html directory with UCID ~joe123. Suppose the contents of the text fields are respectively: "Moe", "999", empty string, and the unchecked checkbox What is the complete URL generated by the browser when this form is submitted in the web.njit.edu environment.

https://web.njit.edu/~joe123/202/my.php?ucid=Moe&pass=999&amount= The empty amount field is sent with no content after the 3rd = symbol. The unchecked checkbox is not even included.

Write a PHP statement that displays "No mail" in the browser if the check box named "mail" on the Assignment 01 form is unchecked (and display "The receipt is in the mail." otherwise.) Assume the PHP script has already executed: $box = $_GET ["mail"]

if ( !isset ($box) ) { echo " No mail." ; } else { echo "The receipt is in the mail." ; } ;

PHP: Suppose you have written a PHP function authenticate ($ucid, $pass, $db) that returns true if the provided $ucid and $pass are in the database table users. Write a PHP statement that uses authenticate to terminate the script and echo "Invalid" on a new line in the browser output if authenticate returns false.

if (! authenticate ($UCID, $pass, $db)) {exit ("<br>Invalid ") ;}

Given a table named transactions [ucid, account, amount, timestamp, mail] with corresponding columns as in Assignment 01, write a simple SQL statement that inserts a row with values joe1 for ucid, 001 for account and 10.95 for amount and no mail receipt requested.

insert into transactions values ('joe', '001', 10.95, NOW(), 'N')

PHP: What is the name of a convenient online site we have used in class designed to detect PHP syntax errors?

meandeviation.com . Another is: phpchecker.com

PHP: What is the most current version of the procedural PHP function library that lets the script interfaces with MySQL?

mysqli_

18.1 what PHP function if any would report that error?

mysqli_error ($db) where $db is the connection to the database.

PHP: Write a single PHP statement that: (1) Outputs a string to the browser (2) Begins by skipping a line in the browser (2) Then outputs the text: Name is: (3) Followed by the value of the variable $amount, then (4) Finally skips another line in the browser.

print "<br>Name is: $amount<br>";

Write the code for the get ( ) function. Just the original version without the code testing for undefined and empty values that we added later.

see Week 04 notes

SQL: Write the SQL statement that retrieves all rows from students that have the column name equal to 'Mary'.

select * from students where name = 'Mary'

(c) All 20 rows since the name could appear in none, any or all of them. You don't know so they all must be searched.

select * from transactions where ucid = 'joe1' and account = '001'

. DB: If a non-unique attribute like name is indexed will it be faster or slower to insert new rows into a database table?

slower (because the indexed data structure must be updated by