Problems with sql query in php

Question

so, I'm using one query to get the profile photo, i'm using the follow query:

$sql = "SELECT photo FROM users WHERE login = '$username'";

I already tried many ways to do, with and without the inverted commas.

$sql = "SELECT `photo` FROM `users` WHERE `login` = '$username'";

Next code is like:

$result = mysqli_query($link, $sql);
echo "<script>console.log('photo: ".$sql."');</script>";

When I check the console i see this error: Uncaught SyntaxError: missing ) after argument list

When I do the query with just "select photo from users" it returns a value.

On another page I use the same code to get the permissions and it returns a value that I want

$sql = "SELECT permission FROM users where login = '$username'";
$result = mysqli_query($link, $sql);
   if ($result->num_rows > 0) {
        while ($row = mysqli_fetch_array($result)) {
        $save = $row[0];
        }
    }

Permission Column is int;

Photo Column is varchar;

Look at the source in the browser. You're passing the query string to your console.log. Though even if you tried to pass in `$result`, you'd get an error because `$result` is a mysqli_result, not `photo`. — aynber, May 14 '20 at 14:29
I know, I just want to check my query to see if the query is right or wrong. — Fernando Filipe, May 14 '20 at 14:35
You should learn to use prepared statements to protect against SQL injection. — Barmar, May 14 '20 at 14:35
That can be a good idea, but you might want to log it to the server instead. When you pass the query to the javascript like this, it will look like ``, which as you see will screw up the single quotes. This is what is causing the javascript error. — aynber, May 14 '20 at 14:36

score 4 · Accepted Answer · answered May 14 '20 at 14:40

4

Since the query contains single quotes, you can't use single quotes around the argument to console.log(), because the quote in the query will terminate the JavaScript string.

Put double quotes around the JS string.

echo "<script>console.log(\"photo: ".$sql."\");</script>";

answered May 14 '20 at 14:40

Barmar

741,623
53
500
612

While this does indeed solve the direct issue, please see my answer as to why this issue should not arise in the first place. `:-)` – Martin May 14 '20 at 14:48
1

I use things like `echo $sql;` and `var_dump($row);` all the time while debugging. I find it more convenient than sending it to the error log. Of course it shouldn't be in production code. – Barmar May 14 '20 at 14:50
Thanks @Barmar, you helped me, I could see my SQL and I noticed i haven't the variable, **$username** right. – Fernando Filipe May 15 '20 at 21:10

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

Your javascript console is wrapped in ' quotes, and your '$username' value is also using these single quotes so this is causing a problem.

Therefore; if you want to export this SQL string to your console, you need to escape these single quotes or to use alternative quotes in your javascript.

This issue is better resolved by Barmar's Answer.

BUT:

Best Practise; you should NOT be outputting SQL strings to your browser at all. This is a potentially severe security hole. Instead (especailly if your SQL server is 'localhost') you should be outputting your SQL data to your PHP error logs:

 $result = mysqli_query($link, $sql);
 //echo "<script>console.log('photo: ".$sql."');</script>";
 error_log("Query Output: ".print_r($sql,true));

Then in your IDE or secured server connection (SFTP etc.) then you can access the PHP Error Logs and view the SQL more safely.

See also: Where does PHP store the error log? (php5, apache, fastcgi, cpanel)

I'm just using the console because it's faster, and i delete always all console commands when I publish the webpage, but thanks for the tip. — Fernando Filipe, May 14 '20 at 14:58
@FernandoFilipe as said, yes it's faster but it is not Best Practise. I hope your browser connections are TLS1.2 secured as a minimum. — Martin, May 14 '20 at 14:59

Problems with sql query in php

2 Answers2

BUT: