Web Conference 2005
Writing PHP for ITS/ASET Web services
Saving Data in SQLite Database Files
Showing source code for /jcd/useful/webcon/2005/sqlite.php | |
<?php
include "header.php";
include "navbar.php";
?>
<p>See also: <a href="../2004/SQLite.html">Lesson on using SQLite from Perl/CGI</a></p>
<p>Today, the most popular and powerful method of storing data
server-side is in relational databases. The most popular method to
access and manipulate data in a database is via the Structured Query
Language (SQL). The vendors of the various Relational Database
Management Systems (RDBMS) each implement their own version of SQL,
which largely resemble each other but still contain significant
differences. Still, a database programmer versed in one variant can
easily learn another.</p>
<p>While there is no fully featured RDBMS yet provided for use with
php.scripts.psu.edu, a database library that implements most of the
SQL92 standard features is available from php.scripts.psu.edu without
need for a database server. SQLite is a database library that does
this by storing data directly into files stored on disk. This lesson
will explore using it.</p>
<ol>
<li><p>Create a new file with the following content:</p>
<p style="border">Update (2008/03/31): NOTE, this file is out of date. Please
consider using the <a href="#pdo-sqlite3">new PDO version</a>.</p>
<?php $tmp = <<<END
<html>
<body>
<?php
// The file to be used as the database:
\$databasefile = "data/sqlite";
// We will recreate the table after the first time so we can easily
// update the table with new collumns in a single page load
if( file_exists(\$databasefile) ){
\$file_exists=1;
}
// This will open a datase for use. If the file does not yet exist,
// it will create it. Note: the script must have write access to
// create the file and update it (it also creates temporary lock files).
if( \$db_handle = sqlite_open(\$databasefile, 0666, \$sqlite_error) ){
if(isset(\$file_exists)){
sqlite_query(\$db_handle, "DROP TABLE table1");
}
sqlite_query(\$db_handle, "CREATE TABLE table1 (fname varchar(32), lname varchar(32), roster int)");
sqlite_query(\$db_handle, "INSERT INTO table1 VALUES ('Jeff','D''Angelo',1)"
);
\$query = sqlite_query(\$db_handle, "SELECT lname,fname,roster from table1 WHERE roster < 3");
\$result = sqlite_fetch_all(\$query);
echo "<table border=1><tr><th>Last Name</th><th>First Name</th><th>Roster #</th></tr>\\n";
foreach (\$result as \$row){
echo "<tr><td>\$row[lname]</td><td>\$row[fname]</td><td>\$row[roster]</td></tr>\\n";
}
echo "</table>\\n";
sqlite_close(\$db_handle);
}else{
die(\$sqlite_error);
}
?>
</body>
</html>
END;
print_code($tmp);
?>
<li><p>Save it as <i class=link>sqlite.php</i> in your space. If you do not have
a "data" folder as set in a former lession, you may get an error
relating to creation of the database file. Make sure the "data" folder
exists, is local to your script (in the same folder), and is writable:</p>
<ul>
<li><p><?=$psuicon?>For a folder to be writable by the PHP server,
it must have Read / Write by the php.scripts.psu.edu group in the ACLs
(<a
href="http://php.scripts.psu.edu/php_set_permissions.html">instructions</a>).
This should have been done during the previous section.</p>
</ul>
<li><p>View the page. You should see a simple table rendered showing
the contents of a table you created in a new database. Successive
loads will recreate the table, so you can modify it and reload the
script to apply changes.</p>
<li><p>To practice learning the <a
href="http://sqlite.org/lang.html">SQL as understood by SQLite</a>,
create a new file with the following content:</p>
<?php $tmp = <<<END
<html>
<body>
<?php
// Relative linked target of <form> action
\$pagename = basename(\$_SERVER['PHP_SELF']);
if(isset(\$_REQUEST['q'])){
\$q = \$_REQUEST['q'];
}else{
\$q = '';
}
// The file to be used as the database:
\$databasefile = "data/sqlite";
if( \$db_handle = sqlite_open(\$databasefile, 0666, \$sqlite_error) ){ ?>
<p>Query you want to do (using <a href="http://sqlite.org/lang.html">
SQL as understood by SQLite</a>):</p>
<form action="<?=\$pagename;?>" method="GET">
<input type="text" name="q" value="<?=htmlspecialchars(\$q);?>" size=100>
<input type="submit">
</form>
<form action="<?=\$pagename;?>" method="GET">
<input type="hidden" name="q" value="">
<input type="submit" value="Clear">
</form>
<?php
if(preg_match("/\w+/",\$q)){
echo "<table border=1>\\n";
\$query = sqlite_query(\$db_handle, \$q);
\$result = sqlite_fetch_all(\$query);
foreach (\$result as \$row){
for(\$i=0;isset(\$row[\$i]);\$i++){
echo "<td>" . htmlspecialchars(\$row[\$i]) . "</td>";
}
echo "</tr>\\n";
}
echo "</table>\\n";
}
sqlite_close(\$db_handle);
}else{
die(\$sqlite_error);
}
?>
<p>cheat sheet:</p>
<ul>
<li>create table bob (id int(32), name varchar(128))
<li>insert into bob values ('1','one')
<li>select * from bob
<li>delete from bob where id='1'
<li>update bob set name='one' where id='1'
<li>drop table bob
<li>select sqlite_version()
<li>select name,sql from sqlite_master where type='table'
</ul>
</body>
</html>
END;
print_code($tmp);
?>
</ol>
<ul>
<li><h3>More on SQLite</h3>
<p>You may read more about the <a
href="http://php.net/manual/en/ref.sqlite.php">SQLite functionality
provided in PHP</a>, including the <a
href="http://php.net/manual/en/function.sqlite-open.php">sqlite_open()</a>,
<a
href="http://php.net/manual/en/function.sqlite-query.php">sqlite_query()</a>
and <a
href="http://php.net/manual/en/function.sqlite-fetch-array.php">sqlite_fetch_array()</a>
functions.</p>
<li><h3>SQLite version 3</h3>
<p>The <a href="http://sqlite.org/">SQLite</a> authors have recently
come out with version 3 of the database library which has numerous
additional features over version 2.</p>
<ul>
<li><p>They have decided the feature changes were large enough to
require a new API and incompatible database file format.</p>
<li><p>PHP has yet to incorporate SQLite version 3. Instead, PHP 5
still bundles SQLite version 2.</p>
<li><p><?=$psuicon?>We have decided to stay
with the version bundled in PHP unless/until we can find a way to
support both library versions with separate API so as not to
corrupt SQLite databases in the version 2 format.</p>
<p class="border">(Updated 2007/04/27): <a
href="http://php.scripts.psu.edu/errata/phase2build4.html">The
Phase II, Build 4 upgrade</a> to <a
href="http://php.scripts.psu.edu/">php.scripts.psu.edu</a> added
support for SQLite version 3 via the new PDO interface.</p>
<div class="border">
<p>(Updated 2008/03/31): The old sqlite extention to PHP may not be
around forever. Since this extention cannot be upgraded
transparently without breaking old database enabled applications,
and the stock RPM based installation we may be switching to does
not appear to support it, we may be dropping support for the native
extention in favor of PDO-only based access to SQLite databases.
</p>
<p><a name="pdo-sqlite3">Use the following example code to practice
accessing a SQLite version 3 database via the newer PDO
interface:</a> </p>
<?php $tmp = <<<END
<html>
<body>
<form>
query:
<input name="query">
<input type=submit>
</form>
<?php
try {
\$dbh = new PDO("sqlite:data/pdo_sqlite3.db");
if(!\$dbh){
print "<p>Error connecting</p>\\n";
die();
}
if(isset(\$_REQUEST["query"])){
\$update["query"] = \$_REQUEST["query"];
}else{
\$update["query"] ='';
}
if(isset(\$update["query"]) && \$update["query"] != ""){
dbquery(\$dbh,\$update);
}
} catch (PDOException \$e) {
print "<p>Error: " . htmlspecialchars(\$e->getMessage()) . "</p>\\n";
die();
}
function dbquery (\$dbh,\$update) {
if(!\$dbh){
print "<p>updatedb(): database handle not ready</p>\\n";
return;
}
\$s = \$dbh->prepare(\$update["query"]);
if(!\$s){
print "<p>dbquery(): database table update prepare error</p>\\n";
return;
}
if(!\$s->execute()){
print "<p>dbquery(): database table update execute error</p>\\n";
return;
}
if( preg_match("/select/", \$update["query"] ) ){
print "<table border=1>";
while(\$row = \$s->fetch(PDO::FETCH_NUM)){
print "<tr>";
foreach (\$row as \$item){
print "<td>" . htmlspecialchars(\$item) . "</td>";
}
print "</tr>";
}
print "</table>";
}
}
?>
<p>cheat sheet (using <a href="http://sqlite.org/lang.html">SQL as understood by SQLite</a>):</p>
<ul>
<li>create table bob (id int(32), name varchar(128))
<li>insert into bob values ('1','one')
<li>select * from bob
<li>delete from bob where id='1'
<li>update bob set name='one' where id='1'
<li>drop table bob
<li>select sqlite_version()
<li>select name,sql from sqlite_master where type='table'
</ul>
</body>
</html>
END;
print_code($tmp);
?>
</div>
</ul>
<li><h3><a name="sqlinjection">SQL injection attack</a></h3>
<p>As will be mentioned in the security lesson, one of the most
problematic security problems with database-powered Web applications is
SQL injection.</p>
<p>To illustrate this phenomenon, let us provide an example. Imagine
you have a PHP site with an SQLite database that contains a table
called <i class=data>roles</i> designating which userids have manager
privileges in the application. It may look like this:</p>
<table border=1>
<tr><th>userid</th><th>role</th></tr>
<tr><td>jcd</td><td>manager</td></tr>
<tr><td>xyz123</td><td>user</td></tr>
</table>
<p>You may have another table named <i class=data>contact_info</i> that lists a
contact email address for each account and may look like this:</p>
<table border=1>
<tr><th>userid</th><th>email</th></tr>
<tr><td>jcd</td><td>jcd@psu.edu</td></tr>
<tr><td>xyz123</td><td>xyz123@psu.edu</td></tr>
</table>
<p>The page to update the email address may look similar to this:</p>
<?php $tmp = <<<END
<html>
<body>
<?php
\$dbfile = "data/app.sqlite";
// For purposes of tutorial illustration, we will create the tables
// from this page.
if( file_exists(\$dbfile) ){
global \$file_exists;
\$file_exists=1;
}
if(isset(\$_POST['new_address'])){
if(\$dbh = sqlite_open(\$dbfile,0666,\$err)){
add_example_table(\$dbh);
sqlite_query(\$dbh, "UPDATE contact_info SET email='" . \$_POST['new_address'] . "' where userid = '" . \$_SERVER['REMOTE_USER'] . "';");
}else{
die("Error opening \$dbfile: \$err");
}
?>
<p>Your email address has been updated.</p> <a href="<?=\$_SERVER['PHP_SELF'];?>">Back</a> to update page.</p>
<?php
}else{
if(\$dbh = sqlite_open(\$dbfile,0666,\$err)){
add_example_table(\$dbh);
\$query = sqlite_query(\$dbh, "SELECT email FROM contact_info where userid = '" . \$_SERVER['REMOTE_USER'] . "';");
\$result = sqlite_fetch_array( \$query );
if(!isset(\$result['email'])){
\$result['email'] = "";
}
}else{
die("Error opening \$dbfile: \$err");
}
?>
You have logged in as <?=\$_SERVER['REMOTE_USER']?>.
<form action="<?=\$_SERVER['PHP_SELF']?>" method=POST>
Update your email address: <input type=text name=new_address value="<?=\$result['email']?>">
<input type=submit value="Update">
</form>
<?php
}
// This is also for illustration
?><h5>table roles</h5><pre><?php
\$query = sqlite_query(\$dbh, "SELECT * from roles");
\$result = sqlite_fetch_all(\$query);
echo htmlspecialchars(print_r(\$result, true));
?></pre><h5>table contact_info</h5><pre><?php
\$query = sqlite_query(\$dbh, "SELECT * from contact_info");
\$result = sqlite_fetch_all(\$query);
echo htmlspecialchars(print_r(\$result, true));
?></pre><?php
function add_example_table(\$dbh)
{
global \$file_exists;
if(!\$file_exists){
sqlite_query(\$dbh, "CREATE TABLE roles (userid varchar(32), role varchar(32))");
sqlite_query(\$dbh, "INSERT into roles values ('jcd','manager')");
sqlite_query(\$dbh, "INSERT into roles values ('xyz123','user')");
sqlite_query(\$dbh, "CREATE TABLE contact_info (userid varchar(32), email varchar(32))");
sqlite_query(\$dbh, "INSERT into contact_info values ('jcd','jcd@psu.edu')");
sqlite_query(\$dbh, "INSERT into contact_info values ('xyz123','xyz123@psu.edu')");
}
}
?>
</body>
</html>
END;
print_code($tmp);
?>
<p>Can you see the problem? This form code is susceptible to attack!
When user xyz123 enters his or her email address, instead of typing in
a benign entry of:</p>
<pre class=code>innocent_user@psu.edu</pre>
<p>he/she could type:</p>
<pre class=code>up2something@psu.edu<i>' where userid='xyz123';</i><b> update roles set role='manager' where userid='xyz123'; --</b></pre>
<p>Normally this would create an SQL query of:
<pre class=code>UPDATE contact_info SET email='innocent_user@psu.edu' where userid = 'xyz123';</pre>
<p>Instead this will create a query of:
<pre class=code>UPDATE contact_info SET email='up2something@psu.edu' where userid='xyz123'<b>;<br> update roles set role='manager' where userid='xyz123'; --</b><i>where userid = 'xyz123';</i></pre>
<p>So the form will successfully update the user's email address, but
it will also diligently carry out the request to change the user's role
to <b>manager</b>, a form of <i>privilege escalation</i>. The <code
class=b>--</code> in SQL is a comment, which effectively nullifies the
rest of the query. This prevents it from causing an error which would
hinder the evil plot.</p>
<p>Instead, the following form may have been better:</p>
<?php $tmp = <<<END
<html>
<body>
<?php
\$dbfile = "data/app.sqlite";
// For purposes of tutorial illustration, we will create the tables
// from this page.
if( file_exists(\$dbfile) ){
global \$file_exists;
\$file_exists=1;
}
if(isset(\$_POST['new_address'])){
// Prevent SQL injection
//
\$old_symbols = array ( "/'/", // match single quotes
"/;.*/" ); // match semicolons and everything after
\$new_symbols = array ( "''", // single quotes become literal ' in SQL
";" ); // remove all potentially malicious code
\$new_address_cleaned = preg_replace(\$old_symbols, \$new_symbols, \$_POST['new_address']);
if(\$dbh = sqlite_open(\$dbfile,0666,\$err)){
add_example_table(\$dbh);
sqlite_query(\$dbh, "UPDATE contact_info SET email='" . \$new_address_cleaned . "' where userid = '" . \$_SERVER['REMOTE_USER'] . "';");
}else{
die("Error opening \$dbfile: \$err");
}
?>
<p>Your email address has been updated.</p> <a href="<?=\$_SERVER['PHP_SELF'];?>">Back</a> to update page.</p>
<?php
}else{
if(\$dbh = sqlite_open(\$dbfile,0666,\$err)){
add_example_table(\$dbh);
\$query = sqlite_query(\$dbh, "SELECT email FROM contact_info where userid = '" . \$_SERVER['REMOTE_USER'] . "';");
\$result = sqlite_fetch_array( \$query );
if(!isset(\$result['email'])){
\$result['email'] = "";
}
}else{
die("Error opening \$dbfile: \$err");
}
?>
You have logged in as <?=\$_SERVER['REMOTE_USER']?>.
<form action="<?=\$_SERVER['PHP_SELF']?>" method=POST>
Update your email address: <input type=text name=new_address value="<?=\$result['email']?>">
<input type=submit value="Update">
</form>
<?php
}
// This is also for illustration
?><h5>table roles</h5><pre><?php
\$query = sqlite_query(\$dbh, "SELECT * from roles");
\$result = sqlite_fetch_all(\$query);
echo htmlspecialchars(print_r(\$result, true));
?></pre><h5>table contact_info</h5><pre><?php
\$query = sqlite_query(\$dbh, "SELECT * from contact_info");
\$result = sqlite_fetch_all(\$query);
echo htmlspecialchars(print_r(\$result, true));
?></pre><?php
function add_example_table(\$dbh)
{
global \$file_exists;
if(!\$file_exists){
sqlite_query(\$dbh, "CREATE TABLE roles (userid varchar(32), role varchar(32))");
sqlite_query(\$dbh, "INSERT into roles values ('jcd','manager')");
sqlite_query(\$dbh, "INSERT into roles values ('xyz123','user')");
sqlite_query(\$dbh, "CREATE TABLE contact_info (userid varchar(32), email varchar(32))");
sqlite_query(\$dbh, "INSERT into contact_info values ('jcd','jcd@psu.edu')");
sqlite_query(\$dbh, "INSERT into contact_info values ('xyz123','xyz123@psu.edu')");
}
}
?>
</body>
</html>
END;
print_code($tmp);
?>
</ul>
<?php
include "navbar.php";
include "footer.php";
?>
|
|
Web Conference 2005Writing PHP for ITS/ASET Web servicesSaving Data in SQLite Database Files |
|
| <- Back - Saving Data in Files | | | Up | | | Tips on Using Remote Databases - Next -> |
See also: Lesson on using SQLite from Perl/CGI
Today, the most popular and powerful method of storing data server-side is in relational databases. The most popular method to access and manipulate data in a database is via the Structured Query Language (SQL). The vendors of the various Relational Database Management Systems (RDBMS) each implement their own version of SQL, which largely resemble each other but still contain significant differences. Still, a database programmer versed in one variant can easily learn another.
While there is no fully featured RDBMS yet provided for use with php.scripts.psu.edu, a database library that implements most of the SQL92 standard features is available from php.scripts.psu.edu without need for a database server. SQLite is a database library that does this by storing data directly into files stored on disk. This lesson will explore using it.
Create a new file with the following content:
Update (2008/03/31): NOTE, this file is out of date. Please consider using the new PDO version.
<html>
<body>
<?php
// The file to be used as the database:
$databasefile = "data/sqlite";
// We will recreate the table after the first time so we can easily
// update the table with new collumns in a single page load
if( file_exists($databasefile) ){
$file_exists=1;
}
// This will open a datase for use. If the file does not yet exist,
// it will create it. Note: the script must have write access to
// create the file and update it (it also creates temporary lock files).
if( $db_handle = sqlite_open($databasefile, 0666, $sqlite_error) ){
if(isset($file_exists)){
sqlite_query($db_handle, "DROP TABLE table1");
}
sqlite_query($db_handle, "CREATE TABLE table1 (fname varchar(32), lname varchar(32), roster int)");
sqlite_query($db_handle, "INSERT INTO table1 VALUES ('Jeff','D''Angelo',1)"
);
$query = sqlite_query($db_handle, "SELECT lname,fname,roster from table1 WHERE roster < 3");
$result = sqlite_fetch_all($query);
echo "<table border=1><tr><th>Last Name</th><th>First Name</th><th>Roster #</th></tr>\n";
foreach ($result as $row){
echo "<tr><td>$row[lname]</td><td>$row[fname]</td><td>$row[roster]</td></tr>\n";
}
echo "</table>\n";
sqlite_close($db_handle);
}else{
die($sqlite_error);
}
?>
</body>
</html>
Save it as sqlite.php in your space. If you do not have a "data" folder as set in a former lession, you may get an error relating to creation of the database file. Make sure the "data" folder exists, is local to your script (in the same folder), and is writable:
For a folder to be writable by the PHP server,
it must have Read / Write by the php.scripts.psu.edu group in the ACLs
(instructions).
This should have been done during the previous section.
View the page. You should see a simple table rendered showing the contents of a table you created in a new database. Successive loads will recreate the table, so you can modify it and reload the script to apply changes.
To practice learning the SQL as understood by SQLite, create a new file with the following content:
<html>
<body>
<?php
// Relative linked target of <form> action
$pagename = basename($_SERVER['PHP_SELF']);
if(isset($_REQUEST['q'])){
$q = $_REQUEST['q'];
}else{
$q = '';
}
// The file to be used as the database:
$databasefile = "data/sqlite";
if( $db_handle = sqlite_open($databasefile, 0666, $sqlite_error) ){ ?>
<p>Query you want to do (using <a href="http://sqlite.org/lang.html">
SQL as understood by SQLite</a>):</p>
<form action="<?=$pagename;?>" method="GET">
<input type="text" name="q" value="<?=htmlspecialchars($q);?>" size=100>
<input type="submit">
</form>
<form action="<?=$pagename;?>" method="GET">
<input type="hidden" name="q" value="">
<input type="submit" value="Clear">
</form>
<?php
if(preg_match("/\w+/",$q)){
echo "<table border=1>\n";
$query = sqlite_query($db_handle, $q);
$result = sqlite_fetch_all($query);
foreach ($result as $row){
for($i=0;isset($row[$i]);$i++){
echo "<td>" . htmlspecialchars($row[$i]) . "</td>";
}
echo "</tr>\n";
}
echo "</table>\n";
}
sqlite_close($db_handle);
}else{
die($sqlite_error);
}
?>
<p>cheat sheet:</p>
<ul>
<li>create table bob (id int(32), name varchar(128))
<li>insert into bob values ('1','one')
<li>select * from bob
<li>delete from bob where id='1'
<li>update bob set name='one' where id='1'
<li>drop table bob
<li>select sqlite_version()
<li>select name,sql from sqlite_master where type='table'
</ul>
</body>
</html>
You may read more about the SQLite functionality provided in PHP, including the sqlite_open(), sqlite_query() and sqlite_fetch_array() functions.
The SQLite authors have recently come out with version 3 of the database library which has numerous additional features over version 2.
They have decided the feature changes were large enough to require a new API and incompatible database file format.
PHP has yet to incorporate SQLite version 3. Instead, PHP 5 still bundles SQLite version 2.
We have decided to stay
with the version bundled in PHP unless/until we can find a way to
support both library versions with separate API so as not to
corrupt SQLite databases in the version 2 format.
(Updated 2007/04/27): The Phase II, Build 4 upgrade to php.scripts.psu.edu added support for SQLite version 3 via the new PDO interface.
(Updated 2008/03/31): The old sqlite extention to PHP may not be around forever. Since this extention cannot be upgraded transparently without breaking old database enabled applications, and the stock RPM based installation we may be switching to does not appear to support it, we may be dropping support for the native extention in favor of PDO-only based access to SQLite databases.
<html>
<body>
<form>
query:
<input name="query">
<input type=submit>
</form>
<?php
try {
$dbh = new PDO("sqlite:data/pdo_sqlite3.db");
if(!$dbh){
print "<p>Error connecting</p>\n";
die();
}
if(isset($_REQUEST["query"])){
$update["query"] = $_REQUEST["query"];
}else{
$update["query"] ='';
}
if(isset($update["query"]) && $update["query"] != ""){
dbquery($dbh,$update);
}
} catch (PDOException $e) {
print "<p>Error: " . htmlspecialchars($e->getMessage()) . "</p>\n";
die();
}
function dbquery ($dbh,$update) {
if(!$dbh){
print "<p>updatedb(): database handle not ready</p>\n";
return;
}
$s = $dbh->prepare($update["query"]);
if(!$s){
print "<p>dbquery(): database table update prepare error</p>\n";
return;
}
if(!$s->execute()){
print "<p>dbquery(): database table update execute error</p>\n";
return;
}
if( preg_match("/select/", $update["query"] ) ){
print "<table border=1>";
while($row = $s->fetch(PDO::FETCH_NUM)){
print "<tr>";
foreach ($row as $item){
print "<td>" . htmlspecialchars($item) . "</td>";
}
print "</tr>";
}
print "</table>";
}
}
?>
<p>cheat sheet (using <a href="http://sqlite.org/lang.html">SQL as understood by SQLite</a>):</p>
<ul>
<li>create table bob (id int(32), name varchar(128))
<li>insert into bob values ('1','one')
<li>select * from bob
<li>delete from bob where id='1'
<li>update bob set name='one' where id='1'
<li>drop table bob
<li>select sqlite_version()
<li>select name,sql from sqlite_master where type='table'
</ul>
</body>
</html>
As will be mentioned in the security lesson, one of the most problematic security problems with database-powered Web applications is SQL injection.
To illustrate this phenomenon, let us provide an example. Imagine you have a PHP site with an SQLite database that contains a table called roles designating which userids have manager privileges in the application. It may look like this:
| userid | role |
|---|---|
| jcd | manager |
| xyz123 | user |
You may have another table named contact_info that lists a contact email address for each account and may look like this:
| userid | |
|---|---|
| jcd | jcd@psu.edu |
| xyz123 | xyz123@psu.edu |
The page to update the email address may look similar to this:
<html>
<body>
<?php
$dbfile = "data/app.sqlite";
// For purposes of tutorial illustration, we will create the tables
// from this page.
if( file_exists($dbfile) ){
global $file_exists;
$file_exists=1;
}
if(isset($_POST['new_address'])){
if($dbh = sqlite_open($dbfile,0666,$err)){
add_example_table($dbh);
sqlite_query($dbh, "UPDATE contact_info SET email='" . $_POST['new_address'] . "' where userid = '" . $_SERVER['REMOTE_USER'] . "';");
}else{
die("Error opening $dbfile: $err");
}
?>
<p>Your email address has been updated.</p> <a href="<?=$_SERVER['PHP_SELF'];?>">Back</a> to update page.</p>
<?php
}else{
if($dbh = sqlite_open($dbfile,0666,$err)){
add_example_table($dbh);
$query = sqlite_query($dbh, "SELECT email FROM contact_info where userid = '" . $_SERVER['REMOTE_USER'] . "';");
$result = sqlite_fetch_array( $query );
if(!isset($result['email'])){
$result['email'] = "";
}
}else{
die("Error opening $dbfile: $err");
}
?>
You have logged in as <?=$_SERVER['REMOTE_USER']?>.
<form action="<?=$_SERVER['PHP_SELF']?>" method=POST>
Update your email address: <input type=text name=new_address value="<?=$result['email']?>">
<input type=submit value="Update">
</form>
<?php
}
// This is also for illustration
?><h5>table roles</h5><pre><?php
$query = sqlite_query($dbh, "SELECT * from roles");
$result = sqlite_fetch_all($query);
echo htmlspecialchars(print_r($result, true));
?></pre><h5>table contact_info</h5><pre><?php
$query = sqlite_query($dbh, "SELECT * from contact_info");
$result = sqlite_fetch_all($query);
echo htmlspecialchars(print_r($result, true));
?></pre><?php
function add_example_table($dbh)
{
global $file_exists;
if(!$file_exists){
sqlite_query($dbh, "CREATE TABLE roles (userid varchar(32), role varchar(32))");
sqlite_query($dbh, "INSERT into roles values ('jcd','manager')");
sqlite_query($dbh, "INSERT into roles values ('xyz123','user')");
sqlite_query($dbh, "CREATE TABLE contact_info (userid varchar(32), email varchar(32))");
sqlite_query($dbh, "INSERT into contact_info values ('jcd','jcd@psu.edu')");
sqlite_query($dbh, "INSERT into contact_info values ('xyz123','xyz123@psu.edu')");
}
}
?>
</body>
</html>
Can you see the problem? This form code is susceptible to attack! When user xyz123 enters his or her email address, instead of typing in a benign entry of:
innocent_user@psu.edu
he/she could type:
up2something@psu.edu' where userid='xyz123'; update roles set role='manager' where userid='xyz123'; --
Normally this would create an SQL query of:
UPDATE contact_info SET email='innocent_user@psu.edu' where userid = 'xyz123';
Instead this will create a query of:
UPDATE contact_info SET email='up2something@psu.edu' where userid='xyz123';
update roles set role='manager' where userid='xyz123'; --where userid = 'xyz123';
So the form will successfully update the user's email address, but
it will also diligently carry out the request to change the user's role
to manager, a form of privilege escalation. The -- in SQL is a comment, which effectively nullifies the
rest of the query. This prevents it from causing an error which would
hinder the evil plot.
Instead, the following form may have been better:
<html>
<body>
<?php
$dbfile = "data/app.sqlite";
// For purposes of tutorial illustration, we will create the tables
// from this page.
if( file_exists($dbfile) ){
global $file_exists;
$file_exists=1;
}
if(isset($_POST['new_address'])){
// Prevent SQL injection
//
$old_symbols = array ( "/'/", // match single quotes
"/;.*/" ); // match semicolons and everything after
$new_symbols = array ( "''", // single quotes become literal ' in SQL
";" ); // remove all potentially malicious code
$new_address_cleaned = preg_replace($old_symbols, $new_symbols, $_POST['new_address']);
if($dbh = sqlite_open($dbfile,0666,$err)){
add_example_table($dbh);
sqlite_query($dbh, "UPDATE contact_info SET email='" . $new_address_cleaned . "' where userid = '" . $_SERVER['REMOTE_USER'] . "';");
}else{
die("Error opening $dbfile: $err");
}
?>
<p>Your email address has been updated.</p> <a href="<?=$_SERVER['PHP_SELF'];?>">Back</a> to update page.</p>
<?php
}else{
if($dbh = sqlite_open($dbfile,0666,$err)){
add_example_table($dbh);
$query = sqlite_query($dbh, "SELECT email FROM contact_info where userid = '" . $_SERVER['REMOTE_USER'] . "';");
$result = sqlite_fetch_array( $query );
if(!isset($result['email'])){
$result['email'] = "";
}
}else{
die("Error opening $dbfile: $err");
}
?>
You have logged in as <?=$_SERVER['REMOTE_USER']?>.
<form action="<?=$_SERVER['PHP_SELF']?>" method=POST>
Update your email address: <input type=text name=new_address value="<?=$result['email']?>">
<input type=submit value="Update">
</form>
<?php
}
// This is also for illustration
?><h5>table roles</h5><pre><?php
$query = sqlite_query($dbh, "SELECT * from roles");
$result = sqlite_fetch_all($query);
echo htmlspecialchars(print_r($result, true));
?></pre><h5>table contact_info</h5><pre><?php
$query = sqlite_query($dbh, "SELECT * from contact_info");
$result = sqlite_fetch_all($query);
echo htmlspecialchars(print_r($result, true));
?></pre><?php
function add_example_table($dbh)
{
global $file_exists;
if(!$file_exists){
sqlite_query($dbh, "CREATE TABLE roles (userid varchar(32), role varchar(32))");
sqlite_query($dbh, "INSERT into roles values ('jcd','manager')");
sqlite_query($dbh, "INSERT into roles values ('xyz123','user')");
sqlite_query($dbh, "CREATE TABLE contact_info (userid varchar(32), email varchar(32))");
sqlite_query($dbh, "INSERT into contact_info values ('jcd','jcd@psu.edu')");
sqlite_query($dbh, "INSERT into contact_info values ('xyz123','xyz123@psu.edu')");
}
}
?>
</body>
</html>
| <- Back - Saving Data in Files | | | Up | | | Tips on Using Remote Databases - Next -> |
|
If you have any questions, feel free to ask me. Content by: Jeff D'Angelo <jcd@psu.edu> © 2005 Last update on: Mon Jun 29, 2009, 8:10:40 PM |
|