can't get result from sql database

Question

I am very new to php and I am trying to create a login system. Here is my code

<?php

    $con=mysqli_connect("XXXXXXX","XXXXXXXX","XXXXXXXXX","XXXXXXXXXX");

    if (!$con)
        {
        echo "failed to connect";
        }

    $username = $_POST['username'];
    $password = $_POST['password'];

    $sql = "SELECT userID FROM users WHERE username = $username and password =          $password;";

    if (!$sql) {
        echo 'query invalid'.mysql_error();
        }

    $result = mysql_query($sql);

    echo "$result";

    $row = mysqli_fetch_array($sql, MYSQLI_ASSOC);

    $active = $row['active'];

    $count = mysqli_num_rows($result);

    mysql_close($con);

?>

I am sure there is no problem with the connection. But my result is not showing up and there is no error message. Previously I had an IF statement that perform further action if the result comes back. Since I am trying to figure out what is going on, I just deleted that part. Somebody please help. Many thanks

He is new to Stack Overflow as well, guys. I editted it in. Be more comprehensive to newcomers. — Phiter, Oct 04 '16 at 00:59
ah, here it is! You forgot the '' around username and password! — Jeff, Oct 04 '16 at 01:00
@PhiterFernandes sorry, I couldn't even see a link or something similar — Jeff, Oct 04 '16 at 01:01
This is very common when people copy paste their code but are unfamiliar with the way SO deals with code. They mostly don't know how to properly format it. So you just click edit and 99% chance the code will be there. — Phiter, Oct 04 '16 at 01:04
@Jeff... maybe `$username` already has the quotes included as part of the value, being passed that way from the form? How can we tell? We can see that the code is using a mix of client interface libraries (mysqli and mysql, which isn't going to work, and when that issue is corrected, the code appears to be vulnerable to SQL Injection. And if the passwords are being hashed, that operation is being performed in the wrong place, on the client rather than the server. The perfect trifecta: 1) invalid mix of mysqli and mysql functions 2) sql injection vulnerabilities, 3) plaintext passwords. — spencer7593, Oct 04 '16 at 01:20
with your current code your `echo 'query invalid'` line wont work. currently `$sql` is a string as such `if (!$sql)` wont ever work because you'll never have an empty string, false boolean, 0 as string or int or a null value. even if it did your mixing `mysql_*` with `mysqli_*` so you wouldn't even get any connection error and because your query hasn't even been executed you can forget about catching any invalid queries with it — Memor-X, Oct 04 '16 at 01:23
[mysqli](http://stackoverflow.com/a/33665819/1816093) and [PDO](http://stackoverflow.com/a/32556010/1816093) of generally what you are trying to do. Includes session, hash, password_verify, exceptions, Prepared Statements. — Drew, Oct 04 '16 at 01:29

score 1 · Answer 1 · edited May 23 '17 at 12:19

1

You're missing the single quotes around the variables, and also you're using mysql mixed with mysqli, which will not work.

$sql = "SELECT userID FROM users WHERE username = '$username' and password = '$password';";
$result = mysqli_query($con, $sql);

$row = mysqli_fetch_assoc($sql); // same as fetch_array with MYSQLI_ASSOC

$active = $row['active'];

$count = mysqli_num_rows($result);

You don't need mysql_close at the end, but if you want to use it, it's mysqli_close($con); Keep in mind this is unsafe.

Use this function to filter user input:

$username = mysqli_real_escape_string($con, $_POST['username']);
$password = mysqli_real_escape_string($con, $_POST['password']);

Read this to make sure your code follows the security standards.

edited May 23 '17 at 12:19

Community

1
1

answered Oct 04 '16 at 01:00

Phiter

14,570
14
50
84

thanks but it still does not work... – 王建捷 Oct 04 '16 at 01:03
Yeah I just noticed you're mixing up mysql with mysqli, I'll update my answer. – Phiter Oct 04 '16 at 01:04
Yes, I will worry about that later though. But do you see any other problem in my code? cuz that function does not work neither – 王建捷 Oct 04 '16 at 01:06
See if it works now – Phiter Oct 04 '16 at 01:07
I have changed all my "mysql" to "mysqli", and it still does not work – 王建捷 Oct 04 '16 at 01:08
it's because most mysqli_ functions need a mysqli_connect as a parameter, in your case the variable $con. It must be the first parameter in them. See my updated answer. – Phiter Oct 04 '16 at 01:10
now it said unable to handle the request – 王建捷 Oct 04 '16 at 01:14
Don't you have php error reporting turned on? – Phiter Oct 04 '16 at 01:17
I am using notepad for coding php then upload to my server – 王建捷 Oct 04 '16 at 01:19
You should install a php server locally if you're learning. Your server probably don't display errors so it will not load the page. – Phiter Oct 04 '16 at 01:20
are there any other way that I can trace the database records? – 王建捷 Oct 04 '16 at 01:25

Zhao.YM · Answer 2 · 2016-10-04T02:05:53.003

0

I recommend this.

$sql = "SELECT userID FROM users 
WHERE username = " ._sql($username) ." and password = " ._sql($password);

// generate sql safe string function 

function _sql($txt) { 
    if ($txt === null || $txt === "") 
      return "NULL"; 
    if (substr($txt, 0, 2) == "##") 
      return substr($txt, 2); 
    //$txt = str_replace("'", "''", $txt); 
    $txt = mysql_real_escape_string($txt); 
    return "'" . $txt . "'"; 
}

edited Oct 04 '16 at 02:05

answered Oct 04 '16 at 01:08

Zhao.YM

61
1
1
11

1

What is this? Why do you recommend this? – chris85 Oct 04 '16 at 01:10
You need to structuring code before start – Zhao.YM Oct 04 '16 at 01:26
1

What is your answer proposing? What is `_sql()`? – chris85 Oct 04 '16 at 01:27
1

I am quite confuzled too – Drew Oct 04 '16 at 01:30
Personally, I'd recommend using a *prepared statement* with *bind placeholders*. I'd also recommend *not* storing plaintext passwords. If we're going to recommend using a function named `_sql`, it might be beneficial to provide some information about it... like the definition of the function, or if it's part of a package, a link to the documentation about how to install the package, etc. AFAIK `_sql` is not a function natively provided by PHP or the mysqli interface library. – spencer7593 Oct 04 '16 at 01:32
Zhao put that in via [edit] not down in comments. If I screwed it up please fix via [edit]. Welcome to the stack. hang in there :p – Drew Oct 04 '16 at 01:55
Sorry for my mistakes, I uploaded new code in a new form. – Zhao.YM Oct 04 '16 at 01:59

can't get result from sql database

2 Answers2