Ajax 2
This presentation is the property of its rightful owner.
Sponsored Links
1 / 64

AJAX-2 PowerPoint PPT Presentation


  • 93 Views
  • Uploaded on
  • Presentation posted in: General

AJAX-2. Integrating Javascript, AJAX, PHP and MySQL. 1. Integrating AJAX, PHP and MySQL. Ex4_1.php grabs a random entry from DB and displays it in an empty field(div) Disables the slot if already filled. Clicking a field in Ex4_1.php calls JS function grabword Grabword runs AJAX

Download Presentation

AJAX-2

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Ajax 2

AJAX-2

Integrating Javascript, AJAX, PHP and MySQL

CS346

1


Integrating ajax php and mysql

Integrating AJAX, PHP and MySQL

  • Ex4_1.php grabs a random entry from DB and displays it in an empty field(div)

  • Disables the slot if already filled.

  • Clicking a field in Ex4_1.php calls JS function grabword

  • Grabword runs AJAX

    • to retrieve a random item from DB and display it in the field or

    • disable the field if filled

2

CS346


Ajax 2

<?php /* ex4_1.php */ ?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<title>Example 4_1</title>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

<link rel="stylesheet" type="text/css" href="style.css" />

<script type="text/javascript" src="functions.js"></script>

</head>

<body>

<?php

for ($i = 1; $i < 9; $i++){

?>

<div class="dborder" id=<?php echo "dborder$i"; ?>

onclick="grabword (this.id)"></div>

<?php

}

?>

</body>

</html>

3

CS346


Notable items in ex4 1 php

Notable items in ex4_1.php

  • PHP tags with a for statement set up a table with 8 elements, each with a different name

  • onclick event used to fire AJAX

    • To grab a word from DB table block randomly

    • Populate the word into the element clicked

      • Each element is in a div tag with an id

      • this.id identifies the element clicked.

4

CS346


Ajax 2

From MySQL Console

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| books |

| cs346 |

| mailinglist |

| mysql |

| products |

| testdb1 |

+--------------------+

7 rows in set (0.14 sec)


Ajax 2

Create a database in WAMP

On cs346, no need to create a database. Just use the one created for you.

mysql> create database ex04a;

Query OK, 1 row affected (0.08 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| books |

| cs346 |

| ex04a |

| mailinglist |

| mysql |

| products |

| testdb1 |

+--------------------+

8 rows in set (0.02 sec)


Ajax 2

Create a table called block in database ex04a

mysql> use ex04a;

Database changed

mysql> create table block (

-> blockid int NOT NULL PRIMARY KEY,

-> content varchar(20)

-> );

Query OK, 0 rows affected (0.27 sec)

mysql> show tables;

+-----------------+

| Tables_in_ex04a |

+-----------------+

| block |

+-----------------+

1 row in set (0.00 sec)


Ajax 2

Check the schema

mysql> describe block;

+---------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| blockid | int(11) | NO | PRI | NULL | |

| content | varchar(20) | YES | | NULL | |

+---------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)


Ajax 2

mysql> insert into block values (1, 'frying');

Query OK, 1 row affected (0.02 sec)

mysql> insert into block values (2, 'awaits');

Query OK, 1 row affected (0.00 sec)

mysql> insert into block values (3, 'similar');

Query OK, 1 row affected (0.00 sec)

mysql> insert into block values (4, 'invade');

Query OK, 1 row affected (0.00 sec)

mysql> insert into block values (5, 'profiles');

Query OK, 1 row affected (0.02 sec)

mysql> insert into block values (6, 'clothes');

Query OK, 1 row affected (0.00 sec)

mysql> insert into block values (7, 'riding');

Query OK, 1 row affected (0.00 sec)

mysql> insert into block values (8, 'postpone');

Query OK, 1 row affected (0.00 sec)

INSERT the entries


Ajax 2

Query the table with a SELECT statement

mysql> select * from block;

+---------+----------+

| blockid | content |

+---------+----------+

| 1 | frying |

| 2 | awaits |

| 3 | similar |

| 4 | invade |

| 5 | profiles |

| 6 | clothes |

| 7 | riding |

| 8 | postpone |

+---------+----------+

8 rows in set (0.00 sec)


Sequence of events

Sequence of events

  • Ex4_1.php displays 8 fields

  • On clicking any field, grabword() in functions.js is called

  • If field is empty, run AJAX call to wordgrabber.php to fill it, otherwise blank the field

  • wordgrabber.php queries the database

  • $querystr = "SELECT content FROM block ORDER BY RAND() LIMIT 1";


Running ex4 1 php

Running ex4_1.php


Ajax 2

Ex4_1.php

onclick

Javascript function grabword(id)

Ajax

wordgrabber.php

include dbconnector.php

call opendatabase()

dbconnector.php

define connection variables

function opendatabase()

13

CS346


Javascript function grabword element id

JavaScript function grabword(element_id)

  • grabword accepts the current object’s id

  • grabword function is in an external JavaScript file

    • An XMLHttpRequest object xmlhttp has been created

  • Run an Ajax request to

    • either populate an empty box

    • Or make the box empty if already populated

14

CS346


Ajax 2

function grabword (theelement){

//If there is nothing in the box, run AJAX to populate it.

if (document.getElementById(theelement).innerHTML.length == 0) {

//Change the background color.

document.getElementById(theelement).style.background = "#CCCCCC";

serverPage = "wordgrabber.php";

var obj = document.getElementById(theelement);

xmlhttp.open("POST", serverPage);

xmlhttp.onreadystatechange = function() {

if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {

obj.innerHTML = xmlhttp.responseText;

}

}

xmlhttp.send(null);

} else {

//Change the background color.

document.getElementById(theelement).style.background = "#FFFFFF";

//If the box is already populated, clear it.

document.getElementById(theelement).innerHTML = "";

}

}

15

CS346


Ajax 2

<?php// wordgrabber.php

// Require in the database connection.

require_once ("dbconnector.php");

// Open the database.

$db = opendatabase();

// Then perform a query to grab a random word from DB table block

// ORDER BY RAND() combined with LIMIT to select a random sample

// from a set of rows:

$querystr = "SELECT content FROM block ORDER BY RAND() LIMIT 1";

if ($myquery = mysql_query ($querystr)) {

$mydata = mysql_fetch_array ($myquery);

echo $mydata['content'];

} else {

echo mysql_error();

}

?>

16

CS346


Ajax 2

<?php//dbconnector.php

//Define the mysql connection variables.

define ("MYSQLHOST", "localhost");

define ("MYSQLUSER", "huen"); // DB username= email username

define ("MYSQLPASS", "student_id");// id without leading 0

define ("MYSQLDB", "huen");// assigned DB=email username

17

CS346


Ajax 2

//dbconnector.php continued

function opendatabase() {

$db = mysql_connect (MYSQLHOST, MYSQLUSER, MYSQLPASS);

try {

if (!$db){

$exceptionstring = "Error connection to database: <br />";

$exceptionstring .= mysql_errno() . ": " . mysql_error();

throw new exception ($exceptionstring);

} else {

mysql_select_db (MYSQLDB,$db);

}

return $db;

} catch (exception $e) {

echo $e->getmessage();

die();

}

}

?>

CS346


Notable about dbconnector php

Notable about dbconnector.php

  • Proprietary DB login information located in only one place

    • Security protection

    • Easy to administer

  • Provides DB connection and selection of DB

  • Exception handling of DB connection

    • Displays error number and error message

19

CS346


Important design precautions in db access

Important Design precautions in DB access

  • Avoid overloading the DB

    • Each click generates an AJAX request to the DB

    • Consider consequences of 3 accesses/per user for a busy site

  • Ergonomics of loading DB result

    • If you have a DB error message, it may spill over the enclosure

    • AJAX tends to load content into small compact space

    • Solution? Email error message to administrator but only a small message to the user

20

CS346


Important design precautions in db access1

Important Design precautions in DB access

  • Security

    • Perhaps Ajax more vulnerable than full-on-page rendered scripts

    • Javascript is visible and may be sniffed out

    • GET requests may introduce code injection – SQL injection

21

CS346


Sql injection

SQL Injection

  • SQL Injection

    • Passing malicious code into the query string to cause problems with any dynamic queries

  • Precaution about the query string!!

    • mysql_real_escape_string

    • Wrap variables from query string with addslashes function for string variables

    • intval function (for integer bases variables)

22

CS346


Sql injection example 1 http www tizag com mysqltutorial mysql php sql injection php

SQL Injection Example 1 http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php

A sample string gathered from a normal user and a bad user trying to use

SQL Injection. We asked the users for their login, which will be used to run

a SELECT statement to get their information.

MySQL & PHP Code:

// a good user's name

$name = "timmy";

$query = "SELECT * FROM customers WHERE username = '$name'";

echo "Normal: " . $query . "<br />";

// user input that uses SQL Injection

$name_bad = "' OR 1'"; // Note the double quotes and single quotes

// our MySQL query builder, however, not a very safe one

$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";

// display what the new query will look like, with injection

echo "Injection: " . $query_bad;

23

CS346


What actually gets built in sql query

What actually gets built in SQL query?

Normal: SELECT * FROM customers WHERE username = 'timmy'Injection: SELECT * FROM customers WHERE username = '' OR 1''

  • Normal query is fine - select everything from customers that has a username equal to timmy as intended.

  • BUT the injection attack made the query behave differently than intended.

  • By using a single quote (') the string part of our MySQL query is ended

  • username = ' '

  • an OR clause of 1 (always true) is added on to our WHERE statement with username = ' ' OR 1

  • This OR clause of 1 will always be trueand so every single entry in the "customers" table would be selected by this statement!

24

CS346


More serious sql injection attacks http www tizag com mysqltutorial mysql php sql injection php

More Serious SQL Injection Attackshttp://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php

MySQL & PHP Code:

$name_evil = "'; DELETE FROM customers WHERE 1 or username = '";

// our MySQL query builder really should check for injection

$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";

// the new evil injection query would include a DELETE statement

echo "Injection: " . $query_evil;

Resulting Query:

SELECT * FROM customers WHERE username = ' '; DELETE FROM customers WHERE 1 or username = ' '

This query with the injected DELETE statement, when run, would completely empty your "customers" table.

25

CS346


How do we avoid this sql injection

How do we avoid this SQL injection?

  • Injection Prevention - mysql_real_escape_string()

  • use function mysql_real_escape_string.

    • takes the string as argument in a MySQL query

    • returns the same string with all SQL Injection attempts safely escaped.

      • Replaces quotes(') a user might enter with a MySQL-safe substitute, an escaped quote \'.

26

CS346


Retry the previous sql attack 1

Retry the previous SQL Attack 1

MySQL & PHP Code:

$name_bad = "' OR 1'";

$name_bad = mysql_real_escape_string($name_bad);

$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";

echo "Escaped Bad Injection: <br />" . $query_bad . "<br />";

Resulting Query

Escaped Bad Injection:SELECT * FROM customers WHERE username = '\' OR 1\''

The DB will just search for a ridiculous username \' OR 1\'

Harm avoided.

27

CS346


Retry the previous sql attack 2

Retry the previous SQL Attack 2

MySQL & PHP Code:

$name_evil = "'; DELETE FROM customers WHERE 1 or username = '"; $name_evil = mysql_real_escape_string($name_evil);

$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";

echo "Escaped Evil Injection: <br />" . $query_evil;

Resulting query

Escaped Evil Injection:SELECT * FROM customers WHERE username = '\'; DELETE FROM customers WHERE 1 or username = \''

Username is the whole string

\'; DELETE FROM customers WHERE 1 or username = \'

28

CS346


Calendar example with ajax and database

Calendar example with Ajax and database

Continue with the Calendar example

CS346

29


1 create a db table

1. Create a DB table

mysql> CREATE TABLE task (

-> taskid INT AUTO_INCREMENT PRIMARY KEY,

-> userid INT,

-> thedate DATE,

-> description TEXT

-> );

Query OK, 0 rows affected (0.06 sec)

30

CS346


Check

Check

mysql> describe task;

+-------------+---------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------------+---------+------+-----+---------+----------------+

| taskid | int(11) | NO | PRI | NULL | auto_increment |

| userid | int(11) | YES | | NULL | |

| thedate | date | YES | | NULL | |

| description | text | YES | | NULL | |

+-------------+---------+------+-----+---------+----------------+

4 rows in set (0.01 sec)

31

CS346


Use php scripts to create insert and display db table in cslabserv1

Use PHP scripts to create, insert and display DB table in CSlabserv1

Re-use the earlier PHP scripts for creating tables, inserting, displaying contents, drop DB and drop tables, now in a directory db

32

CS346


Creating a database table task with scripts in cslabserve1 huen db

Creating a database table task with scripts in cslabserve1/huen/db

33

CS346


Ajax 2

Note: don’t enter a field length for date and int. It will lead to an error for date and restrictive for int.

34

CS346


List tables in database huen

List tables in database huen

35

CS346


Insert records into table task

Insert records into table task

Response

36

CS346


Ajax 2

37

CS346


Ajax 2

38

CS346


Ajax 2

39

CS346


Ajax 2

40

CS346


Query the tasks in calendar

Query the tasks in Calendar

41

CS346


Create another table user

Create another table user

42

CS346


Ajax 2

43

CS346


Adding entries to db table user

Adding entries to DB table user

44

CS346


Add another user john doe to user

Add another user John Doe to user

45

CS346


Displaying contents of db table user

Displaying contents of DB table user

46

CS346


2 use dbconnector php

2. Use dbconnector.php

  • The PHP scripts keep repeating the DB connection script

  • Put the connection in one file dbconnector.php

  • Include this in any application where you need to access database

  • Your code just refers to $db

47

CS346


Ajax 2

<?php//dbconnector.php

//Define the mysql connection variables.

define ("MYSQLHOST", "localhost");

define ("MYSQLUSER", "huen"); // DB username= email username

define ("MYSQLPASS", "student_id");// id without leading 0

define ("MYSQLDB", "huen");// assigned DB=email username

function opendatabase() {

$db = mysql_connect (MYSQLHOST,MYSQLUSER,MYSQLPASS);

try {

if (!$db){

$exceptionstring = "Error connection to database: <br />";

$exceptionstring .= mysql_errno() . ": " . mysql_error();

throw new exception ($exceptionstring);

} else {

mysql_select_db (MYSQLDB,$db);

}

return $db;

} catch (exception $e) {

echo $e->getmessage();

die();

}

}

?>

48

CS346


3 modify autocomplete php

3. Modify autocomplete.php

  • Instead of static array in autocomplete.php, use DB access

  • LIKE and NOT LIKE have two search helper symobls.

    • underscore _ character that looks for one character

    • percentage % character that looks for zero or more characters.

  • $sql = mysql_query("SELECT * FROM table_name WHERE columnname LIKE value%"); while ($row = mysql_fetch_row($sql)) {echo "$row[0] $row[1] $row[2] <br />";}

  • The query will only pick out the rows that provide a TRUE result according to the WHERE equation. The equation will equal the LIKE VALUE plus some possible extra characters afterwards.

49

CS346


Example of like

Example of LIKE

$sql = mysql_query("SELECT * FROM address_book WHERE last_name LIKE 'Stan%'"); while ($row = mysql_fetch_row($sql)) {echo "$row[0] $row[1] $row[2] <br />";}

Search the address_book table and compare all of the data in the last_name column for any values starting with 'Stan' and ending with zero or more characters afterwards.

The LIKE search is not case sensitive, so it will accept anything starting with 'stan' as well.

The WHILE loop then prints out the results found if both equations are found TRUE.

50

CS346


3 modify autocomplete php1

3. Modify autocomplete.php

Protect yourself with mysql_real_escape_string

<?php

//autocomp.php

//Add in our database connector.

require_once ("dbconnector.php");

//And open a database connection.

$db = opendatabase();

$foundarr = array ();

//Setup the dynamic query string.

$querystr = "SELECT name FROM user WHERE name LIKE LOWER('%" . mysql_real_escape_string ($_GET['sstring']) . "%') ORDER BY name ASC";

51

CS346


Autocomplete php modification cont d

autocomplete.php modification cont’d

if ($userquery = mysql_query ($querystr)){

while ($userdata = mysql_fetch_array ($userquery)){

if (!get_magic_quotes_gpc()){

$foundarr[] = stripslashes ($userdata['name']);

} else {

$foundarr[] = $userdata['name'];

}

}

} else {

echo mysql_error();

}

See get_magic_quotes_gpc() in PHP manual and example

52

CS346


Mysql real escape string

mysql_real_escape_string

mysql_real_escape_string — Escapes special characters in a string for use in a SQL statement

Syntax:

string mysql_real_escape_string ( string $unescaped_string [, resource $link_identifier ] )

Description:

Escapes special characters in the unescaped_string , taking into account the current character set of the connection so that it is safe to place it in a mysql_query(). If binary data is to be inserted, this function must be used.

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.

CS346


Another php function addslashes

Another PHP function: addslashes()

string addslashes ( string $str )

Returns a string with backslashes before characters that need to be quoted in database queries etc. These characters are single quote ('), double quote ("), backslash (\) and NUL (the NULL byte).

mysql_real_escape_string is specific to MySQL.

Addslashes is just a general function which may apply to other things as well as MySQL.

CS346


Get magic quotes gpc security feature

get_magic_quotes_gpc – security feature

get_magic_quotes_gpc

(PHP 4, PHP 5)

get_magic_quotes_gpc — Gets the current configuration setting of magic quotes gpc

Description

int get_magic_quotes_gpc ( void )

Returns the current configuration setting of magic_quotes_gpc

Keep in mind that the setting magic_quotes_gpc will not work at runtime.

For more information about magic_quotes, see this security section.

Return Values

Returns 0 if magic quotes gpc are off, 1 otherwise.

55

CS346


Get magic quotes gpc example

get_magic_quotes_gpc() example

<?phpecho get_magic_quotes_gpc();         // 1 => magic quotes are onecho $_POST['lastname'];             // O\'reillyecho addslashes($_POST['lastname']); // O\\\'reillyif (!get_magic_quotes_gpc()) {    $lastname = addslashes($_POST['lastname']);} else {    $lastname = $_POST['lastname'];}echo $lastname; // O\'reilly$sql = "INSERT INTO lastnames (lastname) VALUES ('$lastname')";?>

56

CS346


Autocomplete php modification cont d1

autocomplete.php modification cont’d

//If we have any matches, then we can go through and display them.

if (count ($foundarr) > 0){

?>

<div style="background: #CCCCCC; border-style: solid; border-width: 1px; border-color: #000000;">

<?php

for ($i = 0; $i < count ($foundarr); $i++){

?> <div style="padding: 4px; height: 14px;"

onmouseover="this.style.background = '#EEEEEE'"

onmouseout="this.style.background = '#CCCCCC'"

onclick="setvalue ('<?php echo $foundarr[$i]; ?>')">

<?php echo $foundarr[$i]; ?></div>

<?php

}

?>

</div>

<?php

}

?>

57

CS346


4 modifying the validator php

4. Modifying the validator.php

<?php

//validator.php

//Add in our database connector.

require_once ("dbconnector.php");

//And open a database connection.

$db = opendatabase();

//Setup the dynamic query string.

$querystr = "SELECT userid FROM user WHERE name = LOWER('" . mysql_real_escape_string ($_GET['sstring']) . "')";

58

CS346


4 modifying the validator php cont d

4. Modifying the validator.php cont’d

if ($userquery = mysql_query ($querystr)){

if (mysql_num_rows ($userquery) == 0){

//Then return with an error.

?><span style="color: #FF0000;">Name not found.</span>

<?php

} else {

//At this point we would go to the processing script.

?><span style="color: #FF0000;">Form would now submit.</span>

<?php

}

} else {

echo mysql_error();

}

?>

59

CS346


Autocomplete thru database with ajax

Autocomplete thru database with Ajax

60

CS346


Validation through database with ajax

Validation through database with Ajax

61

CS346


5 modifying taskchecker php for calendar lookup of taks

5. Modifying taskchecker.php for calendar lookup of taks

<?php

//taskchecker.php

//Add in the database connector.

require_once ("dbconnector.php");

//Open the database.

$db = opendatabase();

//Setup the dynamic query string.

$querystr = "SELECT description FROM task WHERE thedate='" . addslashes ($_GET['thedate']) . "'";

62

CS346


Ajax 2

if ($datequery = mysql_query ($querystr)){

if (mysql_num_rows ($datequery) > 0){

?>

<div style="width: 150px; background: #FFBC37; border-style: solid; border-color: #000000; border-width: 1px;">

<div style="padding: 10px;">

<?php

while ($datedata = mysql_fetch_array ($datequery)){

if (!get_magic_quotes_gpc()){

echo stripslashes ($datedata['description']);

} else {

echo $datedata['description'];

}

}

?></div></div>

<?php

}

} else { echo mysql_error();

}

//Close the database connection.

mysql_close ($db);

?>

63

CS346


Tool tip with database and ajax

Tool tip with database and Ajax

64

CS346


  • Login