query error when access clearDB database using php on Heroku

Question

I can access clearDB database well by using Mysql Workbench.

But when I query database by using php on Heroku, it always fail.

This is my code:

$url=parse_url(getenv("CLEARDB_DATABASE_URL"));

$dbhost = $url["host"];
$dbuser = $url["user"];
$dbpass = $url["pass"];
$dbname = substr($url["path"],1);

mysqli_connect($dbhost, $dbuser, $dbpass);

mysqli_select_db($dbname);

$sql = "SELECT * FROM `user_info` WHERE `user_account`='".$user_account."'";

$result = mysqli_query($sql) or die('MySQL query error');

user_account is a table in the database, $user_account is a input variable from client user

help me thanks

Answer 1

You're not passing the link to mysqli_query(). You need to either do that, or use the object oriented style and call query() on the connection.

You also have a possible SQL injection there, because $user_account could contain "foo' OR 1 OR '", returning all rows (and that's just a simple, not very evil case), so you should escape that using mysqli_real_escape_string(), or even better, use prepared statements.

Finally, instead of or die(), how about extracting error information properly, or even configuring mysqli to throw exceptions?

<?php
$url = parse_url(getenv("CLEARDB_DATABASE_URL"));

$server = $url["host"];
$username = $url["user"];
$password = $url["pass"];
$db = substr($url["path"], 1);

$conn = new mysqli($server, $username, $password, $db);

$sql = "SELECT * FROM `user_info` WHERE `user_account`='".$conn->real_escape_string($user_account)."'";

if($result = $conn->query($sql)) {
    foreach($result as $row) {
        // ...
    }
} else {
    throw new Exception($conn->error);
}