Scribd
Upload
76 views31 pages

Advanced SQL Error Handling Guide

The document discusses advanced SQL techniques including error handling, joins, subqueries, and aggregation. It provides examples of using LEFT JOIN to return all rows from the left table even if there is no match on the right table. It also demonstrates using subqueries in the WHERE clause to select records based on the results of another query, and using GROUP BY with aggregate functions like COUNT to group and summarize data. The document contains code examples from a learning tools interchange (LTI) implementation showing the use of complex queries to retrieve and associate data from multiple database tables.

Uploaded by

akbomb
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
76 views31 pages

Advanced SQL Error Handling Guide

The document discusses advanced SQL techniques including error handling, joins, subqueries, and aggregation. It provides examples of using LEFT JOIN to return all rows from the left table even if there is no match on the right table. It also demonstrates using subqueries in the WHERE clause to select records based on the results of another query, and using GROUP BY with aggregate functions like COUNT to group and summarize data. The document contains code examples from a learning tools interchange (LTI) implementation showing the use of complex queries to retrieve and associate data from multiple database tables.

Uploaded by

akbomb
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd

Advanced SQL

Charles Severance
www.php-intro.com
Error Checking So Far
We get away with ignoring errors because they are rare and usually “big”

• Bad database connection

• Bad SQL syntax in a query

• Missing table, missing column – schema / query mismatch

• Missing required parameter

• Violation of a constraint
Start Simple
We just configure PDO to throw an error if anything goes wrong

<?php
$pdo = new PDO('mysql:host=localhost;port=8889;dbname=misc',
'fred', 'zap');
// See the "errors" folder for details...
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

php-intro/code/pdo/pdo.php
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->prepare("SELECT * FROM users where id = :xyz");
$stmt->execute(array(":pizza" => $_GET['id']));
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if ( $row === false ) {
$_SESSION['error'] = 'Bad value for id';
header( 'Location: index.php' ) ;
return;
}

php-intro/code/pdoerrors/error2.php
In Production Environments
• We do not want to have tracebacks in the user interface - may reveal
sensitive data

• We want extensive error logging of any error anywhere in our application


– users will not report errors

• Some errors are subtle and can be affected by user-entered data – length of
VARCHAR field, for example

• People attacking your system “Fuzz Testing” POST weird data

http://en.wikipedia.org/wiki/Fuzz_testing
$sql = "INSERT INTO {$p}sample_map
(context_id, user_id, lat, lng, updated_at)
VALUES ( :CID, :UID, :LAT, :LNG, NOW() )
ON DUPLICATE KEY
UPDATE lat = :LAT, lng = :LNG, updated_at = NOW()";
$stmt = $PDOX->prepare($sql);

$stmt->execute(array(
':CID' => $CONTEXT->id, What could go
':UID' => $USER->id, wrong?
':LAT' => $_POST['lat'],
':LNG' => $_POST['lng']));

$_SESSION['success'] = 'Location updated...';


header( 'Location: '.addSession('index.php') ) ;
return;
tsugi/exercises/map/index.php
http://php.net/manual/en/pdo.prepare.php

http://php.net/manual/en/pdostatement.execute.php
http://php.net/manual/en/pdo.errorinfo.php
$rows = $PDOX->queryDie(
"DELETE FROM {$p}attend WHERE link_id = :LI",
array(':LI' => $LINK->id)
);

function queryDie($sql, $arr=FALSE, $error_log=TRUE) {


tsugi/mod/attend/index.php
$q = FALSE;
$success = FALSE;
$message = '';
try {
$q = $this->prepare($sql);
if ( $arr === FALSE ) {
$success = $q->execute();
} else {
$success = $q->execute($arr);
}
} catch(\Exception $e) {
$success = FALSE;
$message = $e->getMessage();
if ( $error_log ) error_log($message);
}
if ( ! $success ) die('Internal database error');
return $q;
}

tsugi/lib/vendor/Tsugi/Util/PDOX.php queryReturnError()
$sql = "INSERT INTO {$p}sample_map
(context_id, user_id, lat, lng, updated_at)
VALUES ( :CID, :UID, :LAT, :LNG, NOW() )
ON DUPLICATE KEY
UPDATE lat = :LAT, lng = :LNG, updated_at = NOW()";
$stmt = $PDOX->prepare($sql);

$stmt->execute(array(
':CID' => $CONTEXT->id,
':UID' => $USER->id,
':LAT' => $_POST['lat'],
':LNG' => $_POST['lng']));

$_SESSION['success'] = 'Location updated...';


header( 'Location: '.addSession('index.php') ) ;
return;
tsugi/exercises/map/index.php
$stmt = $PDOX->queryDie("INSERT INTO {$p}sample_map
(context_id, user_id, lat, lng, updated_at)
VALUES ( :CID, :UID, :LAT, :LNG, NOW() )
ON DUPLICATE KEY
UPDATE lat = :LAT, lng = :LNG, updated_at = NOW()",
array(
':CID' => $CONTEXT->id,
':UID' => $USER->id,
':LAT' => $_POST['lat'],
':LNG' => $_POST['lng'])
);

$_SESSION['success'] = 'Location updated...';


header( 'Location: '.addSession('index.php') ) ;
return;

tsugi/lib/vendor/Tsugi/Util/PDOX.php
Advanced Queries
LEFT JOIN
• For a normal JOIN, a row is included in the result of the SELECT if and
only if both sides of the ON clause are present.

- The ON clause functions as a WHERE clause.

- The order of the tables in the JOIN clause does not matter.

• A LEFT JOIN removes this restriction.

All the rows from the “left” table that match the WHERE clause are
included whether or not the ON clause finds a row in the “right” table.
Album.title Album.artist_id Artist.artist_id Artist.name

select Album.title, Album.artist_id, Artist.artist_id,Artist.name


from Album join Artist on Album.artist_id = Artist.artist_id
Users Profile

SELECT Users.name,Users.user_id,
Profile.user_id,Profile.laptop
FROM Users JOIN Profile ON Users.user_id = Profile.user_id
Users Profile

SELECT Users.name,Users.user_id, Profile.user_id,Profile.laptop


FROM Users LEFT JOIN Profile ON Users.user_id = Profile.user_id
Example of LEFT JOIN
In the OAUTH 1.x protocols in order to defeat replay attacks, each launch
includes a “Cryptographic Nonce”.

In security engineering, a nonce is an arbitrary number used only once in a


cryptographic communication. It is similar in spirit to a nonce word, hence
the name. It is often a random or pseudo-random number issued in an
authentication protocol to ensure that old communications cannot be reused
in replay attacks.

http://en.wikipedia.org/wiki/Cryptographic_nonce
http://tools.ietf.org/html/rfc5849#section-3.3
LTI Sample Launch Data
lti_version=LTI-1p0
lti_message_type=basic-lti-launch-request
context_id=456434513
context_title=SI301 – PHP
resource_link_id=120988f929-274612
user_id=292832126
roles=Instructor
lis_person_name_full=Charles R. Severance
lis_person_contact_email_primary = [email protected]
tool_consumer_instance_description=University of School
oauth_consumer_key=lmsng.school.edu
oauth_nonce=0ff19a855706012c33233dfb8ecd0c9c
...

http://developers.imsglobal.org/
tsugi/docs/lectures/02-Data-Model-Workbench.mwb
lti_nonce

lti_nonce

SELECT k.key_id, k.key_key, k.secret, n.nonce


FROM lti_key AS k
LEFT JOIN lti_nonce AS n ON k.key_id = n.key_id AND n.nonce = :nonce
WHERE k.key_sha256 = :key
LIMIT 1
SELECT k.key_id, k.key_key, k.secret, k.new_secret, c.settings_url AS key_settings_url,
n.nonce,
c.context_id, c.title AS context_title, context_sha256, c.settings_url AS context_settings_url,
l.link_id, l.title AS link_title, l.settings AS link_settings, l.settings_url AS link_settings_url,
u.user_id, u.displayname AS user_displayname, u.email AS user_email,
u.subscribe AS subscribe, u.user_sha256 AS user_sha256,
m.membership_id, m.role, m.role_override,
p.profile_id, p.displayname AS profile_displayname, p.email AS profile_email,
p.subscribe AS profile_subscribe,
The “big
s.service_id, s.service_key AS service,
r.result_id, r.sourcedid, r.grade, r.result_url
JOIN”
FROM lti_key AS k
LEFT JOIN lti_nonce AS n ON k.key_id = n.key_id AND n.nonce = :nonce
LEFT JOIN lti_context AS c ON k.key_id = c.key_id AND c.context_sha256 = :context
LEFT JOIN lti_link AS l ON c.context_id = l.context_id AND l.link_sha256 = :link
LEFT JOIN lti_user AS u ON k.key_id = u.key_id AND u.user_sha256 = :user
LEFT JOIN lti_membership AS m ON u.user_id = m.user_id AND c.context_id = m.context_id
LEFT JOIN profile AS p ON u.profile_id = p.profile_id
LEFT JOIN lti_service AS s ON k.key_id = s.key_id AND s.service_sha256 = :service
LEFT JOIN lti_result AS r ON u.user_id = r.user_id AND l.link_id = r.link_id
WHERE k.key_sha256 = :key LIMIT 1

tsugi/lib/vendor/Tsugi/Core/LTIX.php loadAllData()
GROUP BY
• Sometimes instead of wanting all of the rows from a table, we want to
count the distinct values of a column.

• This is done with a GROUP BY and aggregation function.

SELECT album_id, COUNT(track_id) FROM Track GROUP BY album_id


Subqueries (use wisely)

Sometimes in a WHERE clause, you want to choose records based on


another query.

SELECT Track.title FROM Track WHERE album_id IN


(SELECT album_id FROM Album WHERE title LIKE '%I%')

http://dev.mysql.com/doc/refman/5.0/en/subqueries.html
An Example from Tsugi

tsugi/mod/peer-grade
SELECT S.submit_id, S.user_id, S.created_at,
count(G.user_id) AS grade_count
FROM {$CFG->dbprefix}peer_submit AS S
LEFT JOIN {$CFG->dbprefix}peer_grade AS G
ON S.submit_id = G.submit_id
WHERE S.assn_id = :AID AND S.user_id != :UID AND S.submit_id NOT IN
( SELECT DISTINCT submit_id from {$CFG->dbprefix}peer_grade
WHERE user_id = :UID)
GROUP BY S.submit_id, S.created_at
ORDER BY grade_count ASC, S.created_at ASC
LIMIT 10

tsugi/mod/peer-grade/peer_util.php loadUngraded()

http://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html
tsugi/mod/peer-grade/admin.php
SELECT S.user_id AS user_id, displayname, email,
S.submit_id as _submit_id,
MAX(G.points) as max_score, MIN(G.points) AS min_score,
COUNT(G.points) as scores,
COUNT(DISTINCT F.flag_id) as flagged,
MAX(S.updated_at) AS updated_at, user_key
FROM {$p}peer_assn AS A
JOIN {$p}peer_submit as S
ON A.assn_id = S.assn_id
JOIN {$p}lti_user AS U
ON S.user_id = U.user_id
LEFT JOIN {$p}peer_grade AS G
ON S.submit_id = G.submit_id
LEFT JOIN {$p}peer_flag AS F
ON S.submit_id = F.submit_id
WHERE A.link_id = :LID
GROUP BY S.submit_id

tsugi/mod/peer-grade/admin.php
SELECT S.assn_id, S.user_id AS user_id, email,
displayname, S.submit_id as submit_id,
MAX(points) as max_points, COUNT(points) as count_points,
C.grade_count as grade_count
FROM {$CFG->dbprefix}peer_submit as S
JOIN {$CFG->dbprefix}peer_grade AS G ON S.submit_id = G.submit_id
JOIN {$CFG->dbprefix}lti_user AS U ON S.user_id = U.user_id
LEFT JOIN (
SELECT G.user_id AS user_id, count(G.user_id) as grade_count
FROM {$CFG->dbprefix}peer_submit as S
JOIN {$CFG->dbprefix}peer_grade AS G
ON S.submit_id = G.submit_id
WHERE S.assn_id = :AID AND G.user_id = :UID
) AS C
ON U.user_id = C.user_id
WHERE S.assn_id = :AID AND S.user_id = :UID

tsugi/mod/peer-grade/peer_util.php computeGrade()
Summary
• More advanced error checking in PDO (it’s complex)

• LEFT JOIN

• GROUP BY

• Subqueries

• AS

• There is still much more...


Acknowledgements / Contributions
These slides are Copyright 2010- Charles R. Severance (www.dr- Continue new Contributors and Translators here
chuck.com) as part of www.wa4e.com and made available under a
Creative Commons Attribution 4.0 License. Please maintain this
last slide in all copies of the document to comply with the
attribution requirements of the license. If you make a change, feel
free to add your name and organization to the list of contributors on
this page as you republish the materials.

Initial Development: Charles Severance, University of Michigan


School of Information

Insert new Contributors and Translators here including names and


dates

You might also like