How Do You Write a PHP Web Page That Asks for a SQL Table Name Then Returns the Contents after the User Clicks a Button?

Problem scenario
You want users to be able to enter a table name and then see the content of the table.  How do you create a webpage with a text field and a submit button that will display the content of the table if it exists?

Solution
Overview
This assumes you have the LAPP stack set up on a single server.  To set up the LAPP stack on Ubuntu, run this command without quotes: "sudo apt-get -y install php5 apache2-bin postgresql postgresql-contrib ​php-pgsql"

This example below challenges the users for Postgres credentials via the web page.  You could hard code a username and password in the viewer1.php page.  You could also leave out the fields for a username and password in the first web page (login1.php).  Then the web page would allow for anonymous users to browse to the site and enter a table name.  The database is hard-coded in viewer1.php, but you could change these two files to work with a database that the user enters.

Warning:  This is not secure against SQL injection. This is for informational purposes only.  This could be done in a secure, development environment or laboratory.

1.  In /var/www/html/ create a file named login1.php.  Have this be the contents:

<html>
<body>
<h2> Want to see a table?  Enter your credentials and the table name.</h2>
<form action="viewer1.php" method="post">
UserName: <input type="text" name="username"><br>
Password: <input type="password" name="password"><br>
TableToView: <input type="text" name="table"<br>
<input type="submit">
</form>

</body>
</html>

2.  In /var/www/html/ create a file named viewer1.php.  Replace "circle" below, but otherwise use the following as the contents:

<?php
   $part1 = $_POST["username"];
   $part2 = $_POST["password"];
   $part3 = $_POST["table"];
   $comp = 'user='.$part1.' password='.$part2 ;
   $host        = "host=127.0.0.1";
   $port        = "port=5432";
   $dbname      = "dbname=circle";
   $credentials = $comp;
   $db = pg_connect( "$host $port $dbname $credentials"  );
   $query = 'select * from ' . $part3 . ';';
   $cc = pg_query($query);
/* This code below was copied from http://razorsql.com/articles/postgresql_column_names_values.html  It was modified somewhat.*/
$i = 0;
echo '<html><body><table><tr>';
while ($i < pg_num_fields($cc))
{
        $fieldName = pg_field_name($cc, $i);
        echo '<td>' . $fieldName . '</td>';
        $i = $i + 1;
}
echo '</tr>';
$i = 0;

while ($row = pg_fetch_row($cc))
{
        echo '<tr>';
        $count = count($row);
        $y = 0;
        while ($y < $count)
        {
                $c_row = current($row);
                echo '<td>' . $c_row . '</td>';
                next($row);
                $y = $y + 1;
        }
        echo '</tr>';
        $i = $i + 1;
   }

pg_free_result($cc);
echo '</table></body></html>';
?>

3.  Go to a web browser.  Go to http://x.x.x.x/login1.php.  Enter the credentials and the table name of the database you want to see.

Leave a comment

Your email address will not be published.