Problem scenario
You want to read in user input via a web page. You then want that user input to participate in an invocation of a SQL command. How do you get user input to be a variable in the construction of a SQL command that is automatically executed when a web-page button is clicked?
Solution
Warning
This does not prevent against SQL injection attacks. This is a proof-of-concept or learning article. It is not meant to be used in production. SQL injection attacks are serious.
Procedures
This example uses the LAPP stack (Linux, Apache, Postgres, and PHP). It assumes that you have installed the correct package for PHP to integrate with Postgresql; to find out how to do this, see this link. There are two PHP files that are used. A SQL command is composed dynamically. This assumes Apache is running with a standard /var/www/html/ directory for browser-accessible web pages.
#1 Create the website page to accept user input. We suggest you name this file good.php. Unless you have a non-standard directory for files for your Apache web server, in /var/www/html/ place this file (e.g., good.php):
<html>
<body>
<form action="good2.php" method="post">
DatabaseName: <input type="text" name="databasename"><br>
Posterity: <input type="text" name="posterity"><br>
<input type="submit">
</form>
</body>
</html>
#2 Create the website page that users will see and behind-the-scenes perform the SQL action. You may or may not need to modify the $host or $port assignments. On a typical LAPP deployment, you would not need to modify them. You will need to modify the $dbname and $credentials variable assignments below. The $credentials must have the privilege to log into the database.
Modify the inside of the quotes in the pg_query stanza to perform the SQL command you want. If you do not change the SQL comand, this .php file will create a database with a name that the user inputted when he/she goes to the .php web page in step #1. This file below (with its first line being <html>) must be called good2.php and be in the same directory as the file in step #1.
<html>
<body>
The table name will be <?php echo $_POST["databasename"]; ?><br>
Posterity: <?php echo $_POST["posterity"]; ?>
<?php $newvar = $_POST["databasename"]; ?>
<!--- The posterity variable is if you want to build on this example. --->
</body>
</html>
<?php // echo "Hello world!";
$host = "host=127.0.0.1";
$port = "port=5432";
$dbname = "dbname=fundb";
$credentials = "user=jdoe password=neatPassword";
$db = pg_connect( "$host $port $dbname $credentials" );
pg_query("CREATE DATABASE $newvar");
?> //end of file
WARNING: The above content as a PHP file is for educational purposes. For security reasons you may not want to place sensitive credentials hard-coded in a file that is accessible from the internet.
3. Browse to the .php file created in step #1 with a web browser (e.g., go to http://x.x.x.x/good.php). A user can enter a name of database and click the "Submit" button. You are done. Please do not expect the web page to reflect success or failure. This is a rudimentary example.
For executing PHP back-end scripts with no web page or front-end component, see this link. For seeing if your SQL commands worked, connect to the database directly and bypass PHP. If you need directions for configuring HeidiSQL, see this link.