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

Question

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.

`$userconn = $row['username'];`...at this point, `$row` is not defined, so you cannot read the username from it. You forgot to fetch a row from the result of your first query. — ADyson, May 27 '22 at 07:57
But you don't need two separate queries for this...learn about INNER JOIN in SQL, instead. — ADyson, May 27 '22 at 07:57
**Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. — ADyson, May 27 '22 at 08:03
https://phpdelusions.net/mysqli also contains good examples of writing safe SQL using mysqli. See also the [mysqli documentation](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) and this: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped or incorrectly quoted input values. If you learnt your current technique from a tutorial or book, please don't use it again. — ADyson, May 27 '22 at 08:03
Think you can just use a nested query: `$rows= mysqli_query($conn, "SELECT * FROM mesajeuseri WHERE username=(SELECT username FROM tb_user WHERE id = '$id2'");` — geertjanknapen, May 27 '22 at 08:03
Or better: `SELECT * FROM tb_user INNER JOIN mesajeuseri ON tb_user.username = mesajeuseri.username WHERE id = ?` (the `?` is of course a placeholder which you'd use combined with a prepared statement and parameter binding!!) — ADyson, May 27 '22 at 08:04
@geertjanknapen there is an error about syntax here, something is not right. — Darius Man22, May 27 '22 at 08:09
@ADyson I appreciated all the things from above, I will take a look about them. — Darius Man22, May 27 '22 at 08:09
@DariusMan22 I missed a `)`. This should work: `$rows= mysqli_query($conn, "SELECT * FROM mesajeuseri WHERE username=(SELECT username FROM tb_user WHERE id = '$id2')")` — geertjanknapen, May 27 '22 at 08:40
@geertjanknapen now is ok no errors but I still don't have any results. Can we speak somewhere where I can describe this more accurated? — Darius Man22, May 27 '22 at 08:46
@DariusMan22 have no way of doing that at the moments since I'm at work. — geertjanknapen, May 27 '22 at 08:53
@geertjanknapen Ok thank you anyway, I will try to figure it out. — Darius Man22, May 27 '22 at 08:56
@DariusMan22 you can [edit] the question to provide any updates and clarifications. That way everyone can help, not just one person. — ADyson, May 27 '22 at 09:53
@ADyson I made the edit now based on responses, the problem is still there, I don't get any data out of the table. — Darius Man22, May 27 '22 at 10:46
Well, what debugging have you done in order to investigate that? Have you checked that `$id2` contains what you expect it to, for example? Have you checked that there any rows in your tb_user table which actually match that value? Then, have you checked if `$userconn` contains what you expect it to, and in turn whether any rows in the `mesajeuseri` table match that value? — ADyson, May 27 '22 at 10:55
@ADyson I found the problem. The value from username row had a space character before text and this generate the "no results" problem. I will check the links you shared with me about SQL injection. Thank you for your patience. — Darius Man22, May 27 '22 at 11:17

score 1 · Answer 1 · edited May 27 '22 at 10:13

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.

score 1 · Accepted Answer · edited May 27 '22 at 10:15

$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.

Ok, I added this to my code "$row = $result->fetch_assoc();" but I still get no results. There are no errors which is good but I can't figure it out why is not working. — Darius Man22, May 27 '22 at 10:34
My bad, the problem was with my table, the data which I wanna sort had before text a space character and the filter doesn't work on it. — Darius Man22, May 27 '22 at 11:15

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

2 Answers2