How Do You Create a Web Page That Issues Customized SQL Commands to a Database Behind-The-Scenes?

Problem scenario
You want a web page with a text field to enter a single line of SQL code.  You want this code (as opposed to some fixed, pre-written code) to execute.  How do you do this?

Solution
This is more dangerous because SQL injection attacks are a serious problem.  This link is how to have a PHP page execute fixed code.   This solution below is not a best practice but designed for illustrative purposes.

This solution below is a rudimentary example and nothing more.  It assumes you have installed LAPP (Linux, Apache, PHP and Postgres) and the PHP plugin for Postgres; for directions on deploying LAPP using Ubuntu see this link.  The solution below assumes that the database service (for Ubuntu, service postgresql start) and web service are running (for Ubuntu, service apache2 start).  It also assumes you have created a Postgres role (e.g., a database user) for which you have a username and password.

This web page will challenge the user for credentials. On this very login page, you can enter SQL code.  When you click submit, the code will run.

1.  Create a file named page1.php in /var/www/html.  Have this as the content:

<html>
<body>

<form action="page2.php" method="post">
UserName: <input type="text" name="username"><br>
Password: <input type="password" name="password"><br>
SQLquery: <input type="text" name="sqlQuery"<br>
<input type="submit">
</form>

</body>
</html>

2.  In /var/www/html/ create a second file named page2.php.  Have this as the content:

<html>
<body>

Username entered: <?php echo $_POST["username"]; ?><br>

<?php $part1 = $_POST["username"]; ?>
<?php $part2 = $_POST["password"]; ?>
<?php $part3 = $_POST["sqlQuery"]; ?>
<?php $comp = 'user='.$part1.' password='.$part2 ; ?>

</body>
</html>

<?php // echo "Hello world!";
   $host        = "host=127.0.0.1"; 
   $port        = "port=5432";
   $dbname      = "dbname=cooldb";
   $credentials = $comp;

   $db = pg_connect( "$host $port $dbname $credentials"  );
   pg_query("$part3");

?> //end of file

3.  Browse to page1.php (e.g., http://x.x.x.x/page1.php where x.x.x.x is the external IP address of the LAPP server).  Enter the credentials and SQL statement.  Please note that you will not see the results of the SQL command with this example.  You can see the results by accessing the database directly (e.g., through a SQL front-end GUI application).

Leave a comment

Your email address will not be published. Required fields are marked *