php SQL select query failing

Question

I can't figure out why my SQL select query keeps failing - I am new to SQL so it might b a stupid mistake but I have not been able to find the answer in the documentation. Thanks a lot in advance

       // connect to db
    $mysqli = new mysqli("127.0.0.1", "maok08ab", "", "Portfolio");
    if ($mysqli->connect_errno) 
    {
        echo "Failed to connect to MySQL";
    }

    $username=$_POST["username"];
    $email=$_POST["email"];
    $hash=$_POST["password"];

    $query = "SELECT * FROM users WHERE username = '$username'";


    $result= mysqli_query($mysqli, $query);

    if ($result == FALSE)
    {
        apologize("something went wrong");
    }

Inside your `if ($result == false)`, put an `echo mysqli_error($mysqli);` and see what that spits out. Also, general error-reporting may be of help. `error_reporting(E_ALL);` `ini_set('display_errors', 1);` — Qirel, Dec 19 '16 at 15:12
As mentioned by @Qirel it is always good to do some error checking. Then come back here and post your errors as a update to your original post. But what my deduction here is that you have either not connected to the database properly (either wrong host, username and password or database name) or there is something wrong with the column 'username' in your table (Probably not this reason). Other than that your query looks fine and should run — PythonNovice, Dec 19 '16 at 15:26
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) — Jay Blanchard, Dec 19 '16 at 15:32
I stnd corrected on the notion that this cannot work because you're not parsing the $username into the query, I've deleted my answer. Still, your Query is prone to injection attacks. http://www.phptherightway.com/#databases — j4k3, Dec 19 '16 at 15:34
@JayBlanchard I don't think its the right place for me to be posting this but I actually love that example. Learnt it at Uni and is a really good example — PythonNovice, Dec 19 '16 at 15:42

score 4 · Accepted Answer · edited May 23 '17 at 12:24

4

Since you're using an OOP connection you have to use OOP to perform the query:

$query = "SELECT * FROM users WHERE username = '$username'";

$result = $mysqli->query($query);

It states very clearly in the manual:

Link Procedural style only: A link identifier returned by mysqli_connect() or mysqli_init()

So your use of $result= mysqli_query($mysqli, $query); will not work as you would think. Make sure to stick with one method (OOP or procedural) throughout the life of your code.

In addition:

Learn about prepared statements for MySQLi. Even escaping the string is not safe! Don't believe it?

Never store plain text passwords! Please use PHP's built-in functions to handle password security. If you're using a PHP version less than 5.5 you can use the password_hash() compatibility pack. Make sure you don't escape passwords or use any other cleansing mechanism on them before hashing. Doing so changes the password and causes unnecessary additional coding.

edited May 23 '17 at 12:24

Community

1
1

answered Dec 19 '16 at 15:51

Jay Blanchard

34,243
16
77
119

Your link for "escaping the string" is not safe enough pertains to `mysql_escape_string`; Is the same said for `mysqli_escape_string` and escaping in general? – Honinbo Shusaku Dec 19 '16 at 15:59
1

Technically, you don't *have* to use OOP -- procedural functions work even if you use `new mysqli(..);`, that quote from the manual just states that the parameter is only used with procedural functions (because you don't have an object to call it on). I agree with you though, it's the best practice not to mix OOP with procedural - but it does work (provided its the same API of course). – Qirel Dec 19 '16 at 16:33
Thanks @JayBlanchard you were right - I must admit I read that in the manual but didn't really pay any attention to it because I didn't understand - I shall read up! Thanks again – Maja Okholm Dec 20 '16 at 08:42

php SQL select query failing

1 Answers1