How to display query results in php

Question

I know how to make a set query display, but I need to take an input, run a statement, and display the result. Here is what I have:

HTML:

<form name="form3" method="post" action="sqlexp.php">
  <input name="sqlstatement" type="text" id="sqlstatement" style="width: 340px;">
  <input type="submit" name="create" value="Create">
</form>

PHP:

ob_start();
$host     = "localhost";    // Host name 
$username = "root";         // Mysql username 
$password = "Passw0rd";     // Mysql password 
$db_name  = "time_tracker"; // Database name 

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password") or die("cannot connect"); 
mysql_select_db("$db_name") or die("cannot select DB");

// Define $sqlstatement
$sqlstatement = $_POST['sqlstatement']; 

// runs statement
$sql = " $sqlstatement ";
$result = mysql_query($sql);
echo $sql;
echo $result;
ob_end_flush();

I also want to make the statements read only so nobody can mess with my tables. im a little new to this i might need some extra explaining

Answer 1

Hello Dear Try this code. I hope it will work for you

<?php
ob_start();
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password="Passw0rd"; // Mysql password 
$db_name="time_tracker"; // Database name 

// Connect to server and select databse.
mysql_connect($host, $username,"")or die("cannot connect"); 
mysql_select_db($db_name)or die("cannot select DB");

// Define $sqlstatement
if(isset($_POST['create']))
{
$sqlstatement=$_POST['sqlstatement']; 
echo $sqlstatement;
}

ob_end_flush();
?>

 <form name="form3" method="post" action="">
<input name="sqlstatement" type="text" value="Test" style="width: 340px;">
<input type="submit" name="create" value="Create">
</form>

Answer 2

To retrieve column names along with the result set, look at this SO question: How to get the columns names along with resultset in php/mysql?

Specifically this code snippet should help you print out the results:

// Print the column names as the headers of a table
echo "<table><tr>";
for($i = 0; $i < mysql_num_fields($result); $i++) {
    $field_info = mysql_fetch_field($result, $i);
    echo "<th>{$field_info->name}</th>";
}

// Print the data
while($row = mysql_fetch_row($result)) {
    echo "<tr>";
    foreach($row as $_column) {
        echo "<td>{$_column}</td>";
    }
    echo "</tr>";
}

echo "</table>";

Now, that said, please be very, very careful before proceeding. It looks like you're unfamiliar with PHP, and what you're attempting is very dangerous as it opens up a classic SQL injection vulnerability: https://xkcd.com/327/

You could restrict the possibility of damage by restricting the database permissions to read-only for the user you're logging in as ($username and $password). Do NOT login as root - never!

If this form is only available to Admins, why not give them a true SQL IDE like Toad or SQL Server Management Studio?

Answer 3

First of you should NEVER pass SQL statements through a form input, it is very insecure. Second use the new mysqli_* or pdo.

Instead you can have a form that enables you to do something, for example create a contact.

You can have a simple form that looks to this:

<form method="post" action="link/to/script.php">
    <input type="text" name="first_name"/>
    <input type="text" name="last_name"/>
    <input type="email" name="email"/>
    <input type="submit" name="create_contact"/>
</form>

Now the PHP script to create the contact:

if(isset($_POST['create_contact'])){ // if submit is set, then create contact
  $firstname=$_POST['first_name'];
  $lastname=$_POST['last_name'];
  $email=$_POST['email'];

  $sql="INSERT INTO contacts (firstname, lastname, email) VALUES (?, ?, ?)"; //query here. The `?` are placeholders
  $stmt = $connection->prepare($sql); //prepare query
  $stmt->bind_param("sss", $firstname, $lastname, $email); // In the first argument, `s` stands for string, there is also `i` for integer
                                                           // Set paramters
  $stmt->execute();  //Execute the query                                                          
}

Now to get a list(array) of contacts in you database and output them.

$sql="SELECT * FROM cotacts"; // SQL statement
$result=mysqli_query($con,$sql); //Do query
$row=mysqli_fetch_array($result,MYSQLI_ASSOC); //Fetch array

//Output array
foreach($row as $contact){
    echo $contact['firstname'];
    echo $contact['lastname'];
    echo $contact['email'];
    }

Never allow a user or anyone to run a SQL that they will be typing out them selves.

Answer 4

Within your database create a privileges column and then on the SQL query use WHERE AND, as a suggestion PDO statments are easy enough to master and more secure than regular SQL

$query = "SELECT type, name FROM something WHERE user = :user AND privlages = 2 ";
$query_params = array(':user' => $user ); 
try{$stmt = $db->prepare($query); $yourArgs= $stmt->execute($query_params);} 
catch(PDOException $ex){} 
$stmt->execute();
$yourArgs = $stmt->fetchAll();
//call array wherever
foreach($yourArgs as $key => $row){
   $type = $row['type'];
   $name = $row['name'];
}

In addition, to create a PDO connection:

 $options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', PDO::ATTR_PERSISTENT => true); 
 try {$db = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8", $username, $password, $options);} 
 catch(PDOException $ex) {die("Failed to connect to the database: " . $ex->getMessage());}   
 $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
 $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);