Use POST method to display data from database PHP

Question

I am using this SQL query in a link to retrieve data from database

<div class="nav-laptop"><a href="proizvodi.php?upit=SELECT Slika, Naziv, Opis, Cijena FROM Proizvodi WHERE Kategorija='Laptop' ORDER BY Proizvodac Asc;">Laptop</a>

and display it using

$sql = $_REQUEST['upit'];

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<div class='proizvodi'>";
    // output data of each row
     $result->data_seek(0);
    while($row = $result->fetch_assoc()) {
        echo "<div class='row'>";
            foreach($row as $key => $value){
                echo "<div class='" . $key . "'>" . $value . "</div>";
            }
        echo "</div>";
        echo "<hr />";
    }
    echo "</div>";
}
else {
    echo "<div class='search-query-none'><img src='index/no result.png' width='754' height='198' /></div>";
}

I realized this is very vulnerable and that I should use POST method to hide parameters from URL. I tried reading online forums, but I found nothing that would help me to convert this to POST way of retrieving data. So, how do I use POST method to achieve the same result as I am achieving right now using GET?

you would have to use a form (easiest way) to send parameters via POST method. Only send the parameters that might change - like the category in your case. And then use prepared statements to not be vulnerable. — Jeff, Feb 02 '16 at 20:04
You should build the query in PHP. Sending queries from the URL is very insecure. If someone found this and loaded `proizvodi.php?upit=delete from Proizvodi` your whole data's table is gone; or possible worse changed all the text to links. — chris85, Feb 02 '16 at 20:04
Take a look at, http://stackoverflow.com/questions/16036041/can-a-html-button-preform-a-post-request and/or https://developer.mozilla.org/en-US/docs/Web/Guide/HTML/Forms/Sending_and_retrieving_form_data. — chris85, Feb 02 '16 at 20:07

Funk Forty Niner · Accepted Answer · 2016-02-02T20:54:09.790

This will give you a general idea on how to do this.

HTML form:

<form method="post" action="your_handler.php">
   <input type = "text" name = "search_query">
   <input type = "submit" name = "submit" value = "Search">
</form>

SQL/PHP and assuming a successful connection using the MySQLi API.

$conn = mysqli_connect("your_host", "user", "password", "db");

if (!$conn) {
    echo "Error: Unable to connect to MySQL." . PHP_EOL;
    echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
    exit;
}

if(isset($_POST['submit'])){

    if(!empty($_POST['search_query'])){

    $search_query = mysqli_real_escape_string($conn, $_POST['search_query']);

    $result = mysqli_query($conn, "SELECT * FROM TABLE WHERE col = '$search_query' ");

    if(!$result) { echo "Error: " . mysqli_error($conn); }

    if ($result->num_rows > 0) {

            while($row = $result->fetch_assoc()) {
                // perform what you want here
                // and check for errors on your query
            }

        }
    }

}

You can substitute SELECT * with the said columns also.

Ideally, a prepared statement is nice to work with.

http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php
http://php.net/pdo.prepared-statements (if you want to look into PDO).

Sidenote: Do not intermix different MySQL APIs such as mysqli_ with PDO. They just don't mix together.

Check for errors also against your query:

http://php.net/manual/en/mysqli.error.php

Add or die(mysqli_error($conn)) to mysqli_query().

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Then the rest of your code

Sidenote: Displaying errors should only be done in staging, and never production.

Plus, make sure that no whitespace gets introduced into your input, otherwise your query may fail.

Use trim() against the input.

Thank you so much! I was having troubles with it last night, and now I managed to make it work finally. Hopefully this is much more safe from SQL injection now than it was before (definitely..): I've got hella lot to learn. — BloodDrunk, Feb 03 '16 at 19:50
@BloodDrunk You're most welcome, glad I could be of help, *cheers!* — Funk Forty Niner, Feb 03 '16 at 19:51

Don't Panic · Answer 2 · 2016-02-02T20:25:14.473

You don't need to use POST for a SELECT query. You can, but it's really better suited for INSERT / UPDATE / DELETE, things that actually change your data. A possible advantage to using a link like that for search results is that it can be saved, bookmarked, emailed, etc., where a form submission cannot. But you are right that putting your entire query into a link like that definitely is extremely vulnerable.

Instead of passing the entire query through the link, you can just pass the parameters, like this:

<a href="proizvodi.php?kategorija=Laptop">Laptop</a>

Then in your display code you can use a prepared statement and safely bind the parameter:

$kategorija = $_GET['kategorija'];

$sql = 'SELECT Slika, Naziv, Opis, Cijena FROM Proizvodi 
        WHERE Kategorija=? ORDER BY Proizvodac';

$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $kategorija);
$stmt->execute();
// etc.

I tried using this, but it return no result hm. Probably query error. — BloodDrunk, Feb 02 '16 at 20:29

Use POST method to display data from database PHP

2 Answers2