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\$
13
  • 4
    \$\begingroup\$ This question is off topic and " all the $post values are checkbox value(its not user input)" is a grave delusion. \$\endgroup\$ Nov 20, 2019 at 14:05
  • \$\begingroup\$ @YourCommonSense any solution? as per your answer how do i use word in array \$\endgroup\$ Nov 20, 2019 at 14:26
  • \$\begingroup\$ Just exactly as shown in the answer. \$\endgroup\$ Nov 20, 2019 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\$ Nov 20, 2019 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, 2019 at 16:45

1 Answer 1

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);
    });
}
\$\endgroup\$
1
  • \$\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\$ Nov 24, 2019 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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