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
– Jeff, Commented Feb 2, 2016 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
– chris85, Commented Feb 2, 2016 at 20:04
Take a look at, stackoverflow.com/questions/16036041/… and/or developer.mozilla.org/en-US/docs/Web/Guide/HTML/Forms/…. — chris85
– chris85, Commented Feb 2, 2016 at 20:07

Funk Forty Niner · Accepted Answer · 2016-02-02 20:54:09Z

2

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.

edited Feb 2, 2016 at 20:54

answered Feb 2, 2016 at 20:10

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

BloodDrunk Over a year ago

Wow, this looks nice. I will take a look into it.

Funk Forty Niner Over a year ago

@BloodDrunk Enjoy! ;-) Cheers

BloodDrunk Over a year ago

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.

Funk Forty Niner Over a year ago

@BloodDrunk You're most welcome, glad I could be of help, cheers!

Don't Panic · Accepted Answer · 2016-02-02 20:25:14Z

2

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.

edited Feb 2, 2016 at 20:25

answered Feb 2, 2016 at 20:14

Don't Panic

41.9k11 gold badges70 silver badges87 bronze badges

1 Comment

BloodDrunk Over a year ago

I tried using this, but it return no result hm. Probably query error.

Collectives™ on Stack Overflow

Use POST method to display data from database PHP

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related