SQL statement in PHP don't operate with the value I give-CodePudding

so I tried to sort data from my SQL table based on username. This concept is based on the user which is connect to the website.

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Profile</title>
    <style type="text/css">
        input{
            border: none;
        }
    </style>
</head>
<body>

<table  cellspacing= 0 cellpadding=10>
        <tr>
            <td>#</td>
            <td>Expeditor</td>
            <td>Mesaj</td>                              
        </tr>
        <?php
            $i=1;
        $id2= $_SESSION["id"];
        $result= mysqli_query($conn, "SELECT * FROM tb_user WHERE id= $id2");
        $row = $result->fetch_assoc();
        $userconn = $row['username'];
        $rows= mysqli_query($conn, "SELECT * FROM mesajeuseri WHERE username='$userconn'")
                                    
        ?>
        <?php foreach ($rows as $row): ?>
        <tr>
            <td><?php echo $i  ; ?></td>
            <td><?php echo $row["user"] ?></td>
            <td><?php echo $row["descriere"]; ?></td>

            <td>
                <a href="">Contact</a>
            </td>
        </tr>
        <?php endforeach; ?>    
</table>
                




</body>
</html>

As you can see I try to use an SQL statement where I use the condition username=$userconn (if I try to echo this value it show the right one). I am not sure if this can work like this. If I leave it like this there will be no result shown, like the table is empty but I have a few rows there with multiple users. This is my table:

And this is my table user:

I know this is not secure because the SQL injection and is not healthy to create my own statement but first I want to see it working as intended.

CodePudding user response：

$row is undefined when you attempt to initialize $userconn. You will need to initialize $row properly, do a

$row = $result->fetch_assoc();

before your

$userconn = $row['username'];

line.

Others already warned you about the dangers of SQL injection and you have explicitly told us in the question that you are aware of this issue and you want to temporarily ignore it, so I will avoid preaching about the dangers of SQL injection. But, once it's working, don't forget about it.

$result= mysqli_query($conn, "SELECT * FROM tb_user WHERE id= $id2");
$userconn = $row['username'];
$rows= mysqli_query($conn, "SELECT * FROM mesajeuseri WHERE username='$userconn'")

Also, you should consider joining the values like

select *
from tb_user
join mesajuseri
on tb_user.username = mesajuseri.username and
   id = 123

This could also be used to easily resolve the SQL injection vulnerability as well, as long as the id should be a number, as you could simply concatenate intval($_SESSION['id']) and that's guaranteed to be a number.

CodePudding user response：

Firstly, $row is not defined prior to you trying to assign something to it

$userconn = $row['username'];

Secondly, you are vulnerable to SQL injection... Consider using an OOP approach using prepared statements for security (where $mysqli = $conn)

$stmt=$mysqli->prepare("SELECT * FROM mesajeuseri WHERE username = ?");
$stmt->bind_param('s',$userconn);
$stmt->execute();
$result=$stmt->get_result();
while($row=$result->fetch_assoc()){
  $rows[]=$row;
}

Thirdly, consider defining which columns you are selecting, instead of using the * selector, as this could lead to an unwanted leak of columns you didn't anticipate, such as if passwords were stored in column

Fourth, you could try using a JOIN... Little more complicated, but it offers seriously fast performance. You would need to research about joins in order to make the correct type of join depending on what you wanted to achieve. Looks like probably INNER JOIN would be fine for what you are trying to do, but I can't say for sure.