Server-side: PHP and MySQL (continued)

(1)

Server-side:

PHP and MySQL (continued)

!

"

2

An observation related to last assignment

Much easier (and without causing problems) is: <div class= "banner">

Transporting Data to a Database... </div>

I found as result code for last assignment:

Some strange aspects:

• Why should you ‘echo’ a literal string that is inside an html-part? • Inside the style class=“banner” is used another style-indication: <h1>. In this

application it was the only thing to put inside the ‘banner’.

Mozilla/Firefox and Internet Explorer treated this ‘mix’ in a different way…

3

# $ % & % '( ) % '* +,)

% $

<?php

echo "hello

\n<br />

hello

\n<br />

" ;

?>

#% ' ) % % % % %

'% ) & % - % &

$ % '% )

#% ' ) % % % ! !

& & & % % %

'% ) &

4 %

. ! &% & $ & %

. % /0 1 #2 & % 3! 4 5 ! % 6 & ! % & % + % $ & % % 5

7

<?php

// Description: Script displays all the information received from a form.

echo“<html><head><title>Received form data</title></head> <body>”; foreach ( $_POST as $field => $value )

{

echo "$field = $value<br>" ;

} echo"</body></html>" ; ?> 8 ! $ !%! ! % % ! % 9:! ;

-So: making use of the associative PHP-$_POST-array 6

<

<html><head><title>Received form data</title></head> <body><h4>Next form data arrived:</h4>

<?php

// Script name: echoformdata.php

// Description: Script displays all the information passed from a form through 'GET' or 'POST'-method if ( !$_REQUEST ) echo "no data received ..." ;

else { if ( $_POST ) {

echo "<h4>by 'Post'-method ...</h4><br />" ;

foreach ( $_POST as $field => $value ) echo "$field = $value<br />";

} if ( $_GET ) {

echo "<h4>by 'Get'-method ...</h4><br />" ;

foreach ( $_GET as $field => $value ) echo "$field = $value<br />";

} } ?> </body></html>

(2)

-Generating (X)HTML-code

through PHP

•

Forms

•

Form-elements

•

Etc.

8

PHP-generating of forms …

<html><head><title>Generated Form Example</title></head><body> <?php

echo "<h4>Generated from an array:<h4/>\n" ;

$fields = array( 'Surname' => 'Ger', 'Length' => 189, 'City'=>'Nijmegen' ) ; echo "<form id=\"myform\" method=\"POST\"

action=\"http://www..../echoformdata.php\">\n"; echo "<table>" ;

foreach ( $fields as $name=> $value) {

echo"<tr><td>$name:</td>

<td><input type=\"text\" name=\"$name\" value=\"$value\" size=\"10\" /></td></tr>\n" ; }

echo"<tr><td><input type =\"submit\" value=\"Submit\" /> </td><td><input type =\"reset\" value=\"Reset\" /></td></tr>"; echo "</table></form>" ;

?> </body></html>

Remains eventually the aspect of data-validation... 9

! ! !

= ! % > ! ! ! ? > ! & % & !%! ! ! ! -<?php

function make_selection_box($name, $array, $default="", $rows=0, $is_multiple=false) {

echo "<select name=\"$name\" "; if( $rows > 0 ) echo " size=$rows"; if( $is_multiple ) echo ' multiple="multiple" '; echo ">\n";

foreach ( $array as $key=>$value ){

echo ' <option value=" '.$key.' " ';

if( $value == $default ) echo ' selected="selected" '; echo ">".$value."</option>\n>";

} echo "</select>\n"; } ?> In case of necessity of ‘nested’ text-delimitors we used here a mix of [sometimes] placing " between ' and ' but [sometimes] using the

escaping character '\' ₁₀

.

!

! ! !

-<html><head><title>Make selection box</title></head> <body>

<h3>Choose your shirt size</h3> <form >

<?php

$shirt_sizes = array ('XS'=>'Extra Small', 'S'=>'Small', 'M'=>'Medium', 'L'=>'Large', 'XL'=>'Extra Large', 'XXL'=>'Extra Extra Large');

$default_size= 'Medium';

make_selection_box('size', $shirt_sizes, $default_size ); ?>

<input type="submit" value="Submit" /> <input type="reset" value="Reset" /> </form></body></html> <?php

function make_selection_box( $name, $array, $default="", $rows=0, $is_multiple=false) { /*….. the shown function code …*/ }

?>

11

<option value="XS">Extra Small</option> <option value="S">Small</option>

<option value="M" selected="selected">Medium</option> <option value="L">Large</option>

<option value="XL">Extra Large</option> <option value="XXL">Extra Extra Large</option> </select>

.

!

! ! !

@ % ! -A -> -#% & % ' ) % $ 9: ; & % ' ) & % ! % % 12

@

! ! !

% ! ! ! ! & ' ) !%! 6 & & % 6 % & ! $ & % ! ! % ! & % % 1 % $ % & % % & % % ' :

(3)

Inserting data into a table

SQL: INSERT INTO <table-name> ...

14

Inserting (new) data into a database

The SQL-command foraddingnew data into a table is:

INSERT INTO table_name( column_name_1, column_name_2, … )

VALUES ( value_1, value_2, … )

In which the sequence (and type and number) of column-names and values must correspond.

Care must be taken that in updating the content of the database all reigning constraints/restrictions are imposed.

For instance: in a library system it cannot be tolerated that a non-existing copy-of-a-book (-number) is borrowed to a non-existing library-member.

If the database management system does not support so-calledforeign keys, then the programmer has to take care of this constraints by manually programming/enforcing them!

15

Some ‘web security’ aspects

• User input with html-tags

• SQL injection

16

Protecting against user input with html-tags

Imagine a simple application with the next form:

</form>

On running it and inputting:

The echoed input is:

Without protection against such kind of input, your website is almost ready to be ‘overtaken’.

An active link!

17

Protecting against user input with html-tags (cont.)

Use:

htmlentities(…)

: converts all applicable characters to HTML entities

<?php // imagine as user input something with html-tags: $userinput = "goto <a href='http://www.ru.nl'>nice place</a>" ; echo "1) " .$userinput." <br>\n" ;

echo "2) " .htmlentities($userinput);

?>

At client-side the result is displayed as:

1) goto <a href='http://www.ru.nl'>nice place</a> <br> 2) goto <a href='http://www.ru.nl'>nice place</a> The arriving generated html-code at client-side was:

The php-function htmlentities(…)will ‘deactivate’ the tags by transforming them into just ‘showable’ html-entities You will also need this function to

show the content of strings with e.g. ‘<‘, ‘&’, ‘ “ ‘, etc.

N.B. htmlspecialchars(..) behaves more or less the same as htmlentities(..).

18

B

• Caused by failure to properly validate user-provided input

•

Allows arbitrary commands to be executed in the database

• Example for a login:

– Username = martijno

– Password = abc123

Sheets on SQL-injection from Martijn Oostdijk

SELECT COUNT ( userID )

FROM users

(4)

19

B

7

Try as username:

hacker' OR 1=1

--SELECT

COUNT ( userID )

FROM

users

WHERE

username = 'hacker' OR 1=1 --

' AND

password = 'justguessing'

Depending on the configuration of php can be possible:

Text-delimitor Token for ‘SQL-comment’ Substitution may result in:

•

Test by inserting string delimiting characters

such as a single quote

•

Look for error messages

Hackers strategy:

20

SQL Injection (3)

Customer Search Tool Zip Code:

S u b m i t

$query = "SELECT name, address, city, state, zip

FROM customers WHERE zip = '$zipcode ' ";

This information is updated every Thursday

6525ED NL Nijmegen Toernooiveld 1 Theo Schouten 6525ED NL Nijmegen Toernooiveld 1 Martijn Oostdijk Zip State City Address Name Imagine:

With the underlying query:

And as result:

21

SQL Injection (4)

Try

as zip: 6525ED' OR 1=1

--SELECT

name, address, city, state, zip

FROM

customers

WHERE

zip = '6525ED' OR 1=1 -- '

Maybe it will be substituted till:

6525ED NL Nijmegen Toernooiveld 1 Theo Schouten 6525ED NL Nijmegen Toernooiveld 1 Martijn Oostdijk 13512 MD Springfield 421 Evergreen St Sue Brown 12345 ID Lakeside 445 6 Ave Jane Peterson 90332 CA Sometown 678 Main St Peter Smith 80202 CO Denver 345 17th St John Doe 80202 CO Denver 123 Main St David Byrne Zip State City Address Name

With the result:

22

B

C

zip: 80202

' UNION

SELECT username, password, null,

null, null FROM users

--SELECT name, address, city, state, zip

FROM customers

WHERE zip = '80202'

UNION

SELECT username, password, null, null, null

FROM users -- '

Or you even can try:

Maybe it will be substituted till:

23

B

D

secret ths abc123 martijno f35.0=(Gd browns jane123 jpeter mary smithp asdf jdoe very_secure byrned 80202 CO Denver 345 17th St John Doe 80202 CO Denver 123 Main St David Byrne Zip State City Address Name

With a possible result like:

24

B

E

• Resources: – http://www.owasp.org/index.php/SQL_Injection – http://www.unixwiz.net/techtips/sql-injection.html – http://www.imperva.com/application_defense_center/white_papers/bli nd_sql_server_injection.html – http://www.ngssoftware.com/papers/advanced_sql_injection.pdf – http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf

(5)

25

SQL injection: some avoiding techniques

1) Check if the given input has the expected data type (e.g. with functions like is_numeric()and using regular expressionssupport).

Check its maximum allowable length.

2) Quote each non numeric user supplied value that is passed to the database with the database-specific string escape function (e.g.

mysql_real_escape_string(..),

mysql_real_escape_string(..)calls MySQL's library function

mysql_real_escape_string, which prepends backslashes to the following characters:

\x00, \n, \r, \, ', "and \x1a.

This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.

Example: // escape username and password for use in SQL $user = mysql_real_escape_string($user);

$pwd = mysql_real_escape_string($pwd);

$sql = "SELECT * FROM users WHERE user='" . $user . "' AND password='" . $pwd . "'"

Some useful

PHP-

date

-functions

• getdate(...)

• checkdate(...)

• date_parse (...)

27

Some useful PHP-

date

-functions

boolcheckdate( int $month, int $day, int $year )

Checks the validity of the date formed by the arguments. A date is considered valid if each parameter is properly defined.

<?php

if ( checkdate( 12, 31, 2000 ) ) echo "a valid date<br />" ; var_dump (checkdate ( 2, 29, 2001 )) ; ?>

Output: a valid date bool(false) arraygetdate( [int $timestamp] )

Returns an associative arraycontaining the date information of the timestamp, or the current local time if notimestampis given.

<?php

$datearray = getdate();

foreach ( $datearray as $key => $value ) echo "<br />$key => $value " ; ?> Output: seconds => 56 minutes => 34 hours => 22 mday => 26 wday => 1 mon => 3 year => 2007 yday => 84 weekday => Monday month => March 0 => 1174941296 Unix-like... 28

Some useful PHP-

date

-functions (2)

date_parse (PHP 5 >= 5.1.3)

Returns associative arraywith detailed info about given date

<?php

$datearray = date_parse( "2006-12-25 10:00:00.5"); foreach ( $datearray as $key => $value )

echo "<br />$key => $value " ;

?> year => 2006 month => 12 day => 25 hour => 10 minute => 0 second => 0 fraction => 0.5 warning_count => 0 warnings => Array error_count => 0 errors => Array is_localtime => <?php $datearray = date_parse("2007-3-26“ );

echo "<br />$datearray[day] / $datearray[month] / $datearray[year]" ; ?>

Output: 26 / 3 / 2007

... and many other useful date-functions ...

29

-= ! F >!! G H% F % = = = G A = - % !-++&&& !%! + % !-++ 5 + + - % !-++&&& I + 30

>

D -

6

Die opdracht gaat deels over het toevoegen van betalingsgegevens aan de database:

(Let op: MySQL wil datums in de vorm jjjj-mm-dd) en anderzijds over het genereren

van een menubox met daarin de verschillende voorkomende plaatsnamen:

Bij invoeren van nieuwe gegevens: data-validation (zowel door client-JS als server-PHP)!