SQL $POST Statement

Hello

What is my mistake in this code? Would you help me?

foreach($db->query("SELECT * FROM uyeler WHERE '{$_POST[arama1]}' = '{$_POST[arama]}' ORDER BY RAND()LIMIT 20") as $row)

The select option value of course would need to be changed to match DB fields, but what do you expect to happen when say “UserName” is selected? Or any other field?

Huge but obvious security holes.

Consider what happens if `$_POST[‘arama1’] contains an SQL command to delete the content of the database - all your data would be easily deleted.

You should:

  1. validate the $_POST fields and move the validated value to a different field so that you can tell that the value is balid.
  2. Keey the SQL and data separate by using prepare instead of query

Missing form tags and name attributes on inputs… Rough example (untested), which I am sure will be ripped apart. :wink:

<?php 
$allowed = array('id', 'usermane', 'sex', 'country', 'age', 'twitter', 'instagram', 'snapchat');
            
$sql = "SELECT id, fmane, sex, country, age, twitter, instagram, snapchat FROM uyeler ";
if(!empty($_POST['search_term']) && !empty($_POST['category']) && in_array($_POST['category'],$allowed)){
    $sql .= "WHERE " . $_POST['category'] . " like :value";
}
$sql .= " ORDER BY RAND()LIMIT 20";

$query = $db->prepare($sql);

if(!empty($_POST['search_term']) && !empty($_POST['category']) && in_array($_POST['category'],$allowed)){
    $search_term = "%".filter_var(trim($_POST['search_term']), FILTER_SANITIZE_SPECIAL_CHARS)."%";
    $query->bindParam(":value", $search_term);
}

$query->execute();
 
$data = array();
while($row = $query->fetch(PDO::FETCH_ASSOC)){
    $data[] = $row; 
}
             
?>
<!DOCTYPE html>
<html lang="en">
<head> 
</head>
<body>
 <div class="container">
 <div class="c_row content">
    <div class="content_left">
        <div class="c_row cf1">
            <h4>Social Media Accounts Directory</h4>
        </div>
        
        <div class="c_row cf2">
            <form action="" method="post">
            <div class="input-group search_bar">
                <select name="category" class="drop_new">
                    <option value="id">ID</option>
                    <option value="username">Username</option>
                    <option value="sex">Sex</option>
                    <option value="country">Country</option>    
                    <option value="age">Age</option>
                    <option value="tritter">Twitter</option>
                    <option value="instagram">Instagram</option>    
                    <option value="snapchat">Snapchat</option>
                </select>
              <input type="text" name="search_term" class="form-control e2" aria-label="..." placeholder="Enter search your key" >
              <span class="input-group-addon e3"><span class="glyphicon glyphicon-search" aria-hidden="true"></span></span>
            </div>
        
            <button type="submit" class="btn btn-default" name="search">Search</button>
            <button type="button" class="btn btn-default refresh_btn" onclick="location.href='index.php'">Refresh</button>
            </form>
        </div>
        
        <div class="c_row cf3">
        
            <table class="table table-striped" align="center" width="850" height="370" cellpadding="7">
                <tr>
                    <th class="t_head">ID</th>
                    <th class="t_head">NAME</th>
                    <th class="t_head">SEX</th>
                    <th class="t_head">COUNTRY</th>
                    <th class="t_head">AGE</th>
                    <th class="t_head">TWITTER</th>
                    <th class="t_head">INSTERGRAM</th>
                    <th class="t_head">SNAPCHAT</th>
                    <th class="t_head">DETAILS</th>
                </tr>
                <?php
                $i=1;
                foreach($data as $row) {
                    if ($i%2!=0)
                        $class="t_light";
                    else
                        $class="t_dark";
                    echo "<tr class='".$class."'><td>" .$row['id'] . "</td>";
                    echo "<td>" .$row['fname'] . "</td>";
                    echo "<td>" .$row['sex'] . "</td>";
                    echo "<td>" .$row['country'] . "</td>";
                    echo "<td>" .$row['age'] . "</td>";
                    echo "<td>" .$row['twitter'] . "</td>";
                    echo "<td>" .$row['instagram'] . "</td>";
                    echo "<td>" .$row['snapchat'] . "</td>";
                    echo ('<td><a href="details.php?id=' .$row['id'] . '" title="Panel">Details</a></td>');
                    echo "</tr>";
                    $i++;
                }
                ?>
            </table>
        </div>
        
    </div>
</body>
</html>

NOTE: Original post was edited, which is where some of this sample page came from.

1 Like

So… the most obvious problem in the statement is that you cant foreach a return from ->query. It returns a singular object.

I don’t know as I’d call it a problem per se, but it is useless because “each” will only be the one return,

can1 what does the form mark-up look like?

I don’t know, gentlemen. Age, Country and others may return multiple results with LIKE. Even id 1,2 etc using LIKE will return multiple rows.

EDIT: Oh forgive me. You are referring to original code.

foreach($db->query("SELECT etc

The form code is much like it is in my example only it was missing actual form tags and name attribute and correct values for select and submit button… that’s all…

1 Like

Well yes, technically you can foreach a singular object, but clearly i meant that foreaching the object does not iterate the rows of the result :stuck_out_tongue:

why not? PDO works like that. and MySQLi as well (as of PHP 5.4).

Ah, yes, I missed seeing the as $row

If the limit and order by were omitted how many records would you expect to match? Would you expect it to match a hell of a lot more over time? If you expect to match a hell of a load of records, ORDER BY RAND doesn’t scale up to well

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.