| | | | | | | |
| Example 1 is a web-enabled spreadsheet that calculates the value of an item of fine jewelry | |
|
| | |
| | | | | | | |
| Example 2 is a web-enabled SQL database showcasing various components and capabilities | |
|
| | |
|
| Example 3 searches 1,000,000 company records and pages results ("%" or "_" are wildcards). | |
| |
| Enter Part of a Company Name | Sort By | Direction | Offset | | |
| | | | | | |
| |
| | |
| |
| | | | | | | |
| | | | | | | |
| | | | | | | |
| | | | | | | |
| | | | | | | |
| | | | | | | |
| | | | | | | |
| | | | | | | |
| | | | | | | |
| | | | | | | |
| |
| These scripts both return the results above using a whitelist method that, used with prepared statements, protects against SQL injection. | |
| |
| <?php header("Access-Control-Allow-Origin: *"); header("Content-Type: application/json; charset=utf8mb4"); header("Cache-Control: no-cache, must-revalidate"); header("Expires: Sat, 26 Jul 1997 05:00:00 GMT"); $host = '127.0.0.1'; $db = 'databasename'; $user = 'databaseusername'; $pass = 'databaseuserpassword'; $charset = 'utf8mb4'; $options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => TRUE, ]; $dsn = "mysql:host=$host;dbname=$db;charset=$charset"; try { $pdo = new PDO($dsn, $user, $pass, $options); } catch (PDOException $e) { echo 'Connection failed: ' . $e->getMessage(); } $searchterm = $_GET["searchterm"]; $whitelist = ["Company","Address","City","County","ZIP","Phone1"]; $selection = array_search($_GET['orderby'], $whitelist); $orderby = $whitelist[$selection]; $direction = $_GET['direction'] == 'DESC' ? 'DESC' : 'ASC'; $limit = 10; $offset = $_GET["offset"]; $stmt = $pdo->prepare("SELECT Company, Address, City, County, ZIP, Phone1 FROM customers WHERE Company LIKE :searchterm ORDER BY ".$orderby." ".$direction." LIMIT :limit OFFSET :offset"); $stmt->bindValue(':searchterm', "$searchterm%"); $stmt->bindValue(':limit', (int) $limit, PDO::PARAM_INT); $stmt->bindValue(':offset', (int) $offset, PDO::PARAM_INT); $stmt->execute(); $data = $stmt->fetchAll(); $json = json_encode($data); echo $json; ?> | |
| |
| <?php header("Access-Control-Allow-Origin: *"); header("Content-Type: application/json; charset=utf8mb4"); header("Cache-Control: no-cache, must-revalidate"); header("Expires: Sat, 26 Jul 1997 05:00:00 GMT"); $host = '127.0.0.1'; $db = 'databasename'; $user = 'databaseusername'; $pass = 'databaseuserpassword'; $charset = 'utf8mb4'; $options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => FALSE, ]; $dsn = "mysql:host=$host;dbname=$db;charset=$charset"; try { $pdo = new PDO($dsn, $user, $pass, $options); } catch (PDOException $e) { echo 'Connection failed: ' . $e->getMessage(); } $whitelist = ["Company","Address","City","County","ZIP","Phone1"]; $selection = array_search($_GET['orderby'], $whitelist); $orderby = $whitelist[$selection]; $direction = $_GET['direction'] == 'DESC' ? 'DESC' : 'ASC'; $searchterm = $_GET["searchterm"]; $limit = 10; $offset = $_GET["offset"]; $stmt = $pdo->prepare('SELECT Company, Address, City, County, ZIP, Phone1 FROM customers WHERE Company LIKE ? ORDER BY '.$orderby.' '.$direction.' LIMIT ? OFFSET ?'); $stmt->execute(["%$searchterm%", $limit, $offset]); $data = $stmt->fetchAll(); $json = json_encode($data); echo $json; ?> | |
| |