0

I am just wondering, if possible, the best way to go about allowing users to actually input an SQL query from within a web application.

I have so far got a very simple web application that allows users to view the database tables and manipulate them etc etc..

I wanted to give them an option to actually type queries from within the web app too (SELECT * FROM).. and then display the results in a table. (Exactly the same as a search bar, but I don't think that would cut it, would it?).

I am only using PHP at the moment, is what I'm looking to do possible with just HTML/PHP or will I need the help of other languages?

This may be too complex for me, but if someone could give me a starting point that would be great, thank you.

UPDATE: From my understanding to answer my question, i need something like: 

<form action= Search.php method="POST">
<input type="text" name="Search">
<input type="submit" name"">

Search.php

<?php
if ($_SERVER["REQUEST_METHOD"] == "POST") {

$SEARCH = $_POST['Search'];

if (!isset($_POST)) {

    $sql = "'%".$_POST['$SEARCH']."%'";

    $results = mysqli_query($con, $sql);

            echo "<table border ='2'>";

            if (mysqli_num_rows($results) !=0) {

            while ($row=mysqli_fetch_array($results)) {

            echo "<tr><td></td></tr>";

            }

            echo "</table>";
        }else {
            echo "Failed! Try another search query.";
            }
    }
}



    ?>

At the moment in returns one error:

Undefined index: Search

It's talking about the $SEARCH = $_POST['Search'];

But I thought I am defining that Search, as that's the Search in the form?

Tipping44
  • 281
  • 4
  • 16
  • 6
    Why not just give them access to something like phpMyAdmin? – ceejayoz Oct 28 '17 at 23:01
  • @ceejayoz I understand your point totally :) I know it's something that would never be executed in the real world, I'm well aware of the potential security risks, however I am fairly new to php/sql and just the thought of this has me questioning how one would go about it, so just having a "play" if you like. – Tipping44 Nov 01 '17 at 12:32
  • If you want something a little simpler than phpMyAdmin to go off as an example/inspiration, check out https://github.com/vrana/adminer. – ceejayoz Nov 01 '17 at 12:35
  • thanks for the reply.... the phpMyAdmin example is essentially what I am looking for, the text box and a search button is literally so the user can execute the SQL statement, like a search query. What I am having difficulties with is simply knowing what type of SQL statement I am supposed to write in the execute script, for example: Where in a search statement someone could write: `$sql = SELECT * FROM user WHERE name LIKE ='%".$_POST['search']."%'` Then they would return the results. I instead need to type something like : `$sql = "'%".$_POST['$SEARCH']."%'";` – Tipping44 Nov 01 '17 at 13:14
  • If you want to let the user make raw SQL queries, just do `$sql = $_POST['whatever'];` – ceejayoz Nov 01 '17 at 13:47
  • Gah! just made like 4 edits to that post, sorry! Pasted in the wrong part of the code... The update shows what I have so far, it's just that one error at the moment. – Tipping44 Nov 01 '17 at 14:06
  • There's some screwy PHP in there. `$results = mysqli_query($con, $_POST['Search']);` is all you need. Capitalization matters, and you're mixing up strings and variables. – ceejayoz Nov 01 '17 at 14:08
  • Hmm, That's confused me ...added what you have said, no more errors, just a blank page......do you know where I am echoing the , td<>, is that why it's blank? I don't get how to echo it out , so usually I would say: `echo "".$row['Customer_ID']."` but because I do not know what's being searched I don't know what to put here :/ – Tipping44 Nov 01 '17 at 14:35
  • A blank page is often a sign of error messages lurking in your logs. – ceejayoz Nov 01 '17 at 14:35
  • If that is all the code you have, other than what @ceejayoz already said you're missing the part where you connect to your database. Where does `$con` come from? – rickdenhaan Nov 01 '17 at 17:19
  • I have a seperate php file for that one, (connection.php) that gets called fine. Like I mentioned, I have made a fully working (all be-it, not very secure) web application where you can control the entire database, this small bit is just another feature I wanted to implement. I am seeing this similar to a search type php script, yet I can't understand how to signify that the sql statement I want too use is the one the user has typed (similar to what i mentioned in a previous comment) – Tipping44 Nov 01 '17 at 17:54

2 Answers2

3

Sounds like you're building your own minimalistic version of phpMyAdmin. That's perfectly doable with just PHP and HTML.

A very basic implementation would be a standard HTML form with a textarea, which submits to a PHP script that executes the query and renders a table of the results. You can get the required table column headers from the first result row's array keys if you fetch the results as an associative array.

You may (or perhaps I should say "will") run into situations where users provide a query that returns millions of results. Outputting all of them could cause browsers to hang for long periods of time (or even crash), so you might want to implement some sort of pagination and append a LIMIT clause to the query.

Since the user is providing the SQL query themselves, they need to know what they did wrong so they can correct it themselves as well so you'll want to output the literal error message from MySQL if the query fails.

Allowing users to provide raw SQL queries opens the door to a lot of potential abuse scenarios. If it were my application, I would not want users to use this feature for anything other than SELECT queries, so I would probably have the user-provided queries executed by a MySQL-user that only has SELECT privileges on the application database and not a single other privilege -- that way any user that tries to DROP a table will not be able to.

rickdenhaan
  • 10,857
  • 28
  • 37
0

Undefined index: Search

This error will show only when the PHP is executed for the first time as it's simply expecting "Search" in $_POST.

$_SERVER['REQUEST_METHOD'] checks if the request method is POST it does not check if $_POST have any post data in it. (Source :$_POST vs. $_SERVER['REQUEST_METHOD'] == 'POST')

But the page is being loading for the first time so it wouldn't have anything in POST.

You can simply avoid it by check if the page is loading for first time, using the "isset()" method.

If its loading for the first time just ignore the further execution of php code and simply show the form to enter the query.

<?php
if(isset($_POST['Search']))
{ 
`// Query execution code`.
} 
?>

<form action= Search.php method="POST">
<input type="text" name="Search">
<input type="submit" name"">

So if the search index is not set in the $_POST it wont execute the php code and will not generate any error.

Ritter7
  • 166
  • 2
  • 14