1
\$\begingroup\$

I use the following code to filter data from SQL and it works fine. However, below code is vulnerable to SQL injection, but all the $post values are checkbox value (it's not user input). So can I use below code?

Or do I need to use "prepared statement" as per this answer? I can only send number in array.

Conn.php

    try {

    //create PDO connection 
    $conn = new PDO("mysql:host=" . DBHOST . ";port=3306;dbname=" . DBNAME, DBUSER, DBPASS);
    $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $conn->exec("SET CHARACTER SET utf8");
} catch (PDOException $e) {
    //show error
    echo '<p class="bg-danger">' . $e->getMessage() . '</p>';
    exit;
}

INDEX

<div class="md-radio my-1">
     <input type="radio" class="filter_all cate" name="cate" id="car" value="Car">
     <label for="car">Car</label>
</div>

script

$(document).ready(function () {
            filter_data();
            function filter_data() {
                $('.filter_data');
                var action = 'fetch_data';
                var cate = get_filter('cate');
                $.ajax({
                    url: "fetch.php",
                    method: "POST",
                    data: {
                        action: action,
                        cate: cate
                    },
                    success: function (data) {
                        $('.filter_data').html(data);
                    }
                });
            }
            function get_filter(class_name) {
                var filter = [];
                $('.' + class_name + ':checked').each(function () {
                    filter.push($(this).val());
                });
                return filter;
            }
            $('.filter_all').click(function () {
                filter_data();
            });
        });

fetch.php

if (isset($_POST["action"])) {

 $query = "SELECT * FROM allpostdata WHERE sts = '1'";

if (isset($_POST["cate"])) {
    $cate = implode("','", $_POST["cate"]);
    $query .= "AND sca IN('" . $cate . "')";
}

$stmt = $conn->prepare($query);
$stmt->execute();
$result = $stmt->fetchAll();
$total_row = $stmt->rowCount();
$output = '';
if ($total_row > 0) {
    foreach ($result as $row) {
    $output .= '<a href="/single_view.php?p=' . $row['link'] . '>' . $row['link']. '</a>
 }
} else {
    $output = '<h3>No Data Found</h3>';
}
echo $output;
}
\$\endgroup\$
  • 4
    \$\begingroup\$ This question is off topic and " all the $post values are checkbox value(its not user input)" is a grave delusion. \$\endgroup\$ – Your Common Sense Nov 20 '19 at 14:05
  • \$\begingroup\$ @YourCommonSense any solution? as per your answer how do i use word in array \$\endgroup\$ – sanoj lawrence Nov 20 '19 at 14:26
  • \$\begingroup\$ Just exactly as shown in the answer. \$\endgroup\$ – Your Common Sense Nov 20 '19 at 14:27
  • 1
    \$\begingroup\$ Here is my instruction on how to debug your database interactions. Please follow it to find out why doesn't your query find the data \$\endgroup\$ – Your Common Sense Nov 20 '19 at 14:41
  • 1
    \$\begingroup\$ Please do not update questions after they have been answered, especially please do not update code after the question has been answered. Please see codereview.stackexchange.com/help/someone-answers. \$\endgroup\$ – pacmaninbw Nov 24 '19 at 16:45
1
\$\begingroup\$

Applying YCS's tutorial can't be dumbed down any further, so I suppose I'll write it out.

if (isset($_POST["action"])) {
    $query = "SELECT link FROM allpostdata WHERE sts = '1'";

    if (!empty($_POST["cate"])) {
        $query .= " AND sca IN (" . str_repeat("?,", count($_POST["cate"]) - 1) . "?)";
    } else {
        $_POST["cate"] = []; // in case it is not set 
    }

    $stmt = $conn->prepare($query);
    $stmt->execute($_POST["cate"]);
    $result = $stmt->fetchAll();
    if (!$result) {
        exit('<h3>No Data Found</h3>');
    }
    foreach ($result as $row) {
        echo "<a href=\"/single_view.php?p={$row['link']}\">{$row['link']}</a>";
    }
}

I assume this will display your links side by side. You may want to style them a bit to position the links in a user friendly fashion.

Additionally, $('.filter_data'); is doing nothing and can be removed.

I'd probably change the filter_data() declaration to something more brief:

function filter_data() {
    $.post(
       "fetch.php",
       {action: 'fetch_data', cate: get_filter('cate')}
    )
    .done(function(data) {
        $('.filter_data').html(data);
    });
}
| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ a small change in question what if i have multiple if statement in query, and what about SQL injection there is no need of using prepared statement \$\endgroup\$ – sanoj lawrence Nov 24 '19 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.