Quick question. - Accessing Database with MySQL and PHP

[GumbiRo]

Hello everyone, this is a quick question.


Im trying to look for input that the user made and validate if there's a duplicate or not.

so:
Steps:
1.check user input.
2.Check database for duplicate.
3.Return false/true.


Here's what I tried to do...

include 'db_connect.php'

$username = $_POST['username'];
$result = mysql_query("SELECT 1 FROM members WHERE username = " . mysql_real_escape_string($username)); 
if ($result && mysql_num_rows($result) > 0) { 
$ready = false; 
} else { 
$ready = true; 
}

Here's what Im getting:

 

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'adminUser'@'localhost' (using password: NO) in/home1/../../../../sec_reg.php on line 9

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in /home1/../../../../sec_reg.php on line 9

$result=my.... is line 9.


Now,  what I find weird is that in the SAME file I've got this:
 

if ($insert_stmt = $mysqli->prepare("INSERT INTO members (username, email) VALUES (?, ?)"))
{
..Some code here
}

And before I added the code with the warning the mysqli->prepare didn't have any problems. What could be happening?


**Yes...I checked that db_connect.php was correct.

 

 

Edited December 11, 2013 by GumbiRo

[Barand]

try using mysqli_real_escape_string() as you are using a mysqli connection

[GumbiRo]

try using mysqli_real_escape_string() as you are using a mysqli connection

 

 

...I thought I did...? 

 

$result = mysql_query("SELECT 1 FROM members WHERE username = " . mysql_real_escape_string($username)); 

But that doesn't solve my problem....

Edited December 11, 2013 by GumbiRo

[Barand]

 

 

if ($insert_stmt = $mysqli->prepare("INSERT INTO members (username, email) VALUES (?, ?)"))
{
..Some code here
}

 

You say the above code worked and that is using mysqli.

 

You cannot mix mysql and mysqli functions using the same connection.

[GumbiRo]

You say the above code worked and that is using mysqli.

 

You cannot mix mysql and mysqli functions using the same connection.

Oh ok I didn't quite get that from you.

 

Ok, so what do you suggest I change this to?

 

$result = mysql_query("SELECT 1 FROM members WHERE username = " . mysql_real_escape_string($username)); 

Omg...I just saw my error, I thought I was using mysqli.... hahaha Let me change it and I'll get back to you in a second.

Edited December 12, 2013 by GumbiRo

[Barand]

You cannot mix mysql and mysqli functions using the same connection.

[GumbiRo]

 

You cannot mix mysql and mysqli functions using the same connection.

 

Yeah replied without looking thoroughly with my glasses on sorry!

 

 

But now I've gt this error:

 

 

Warning: mysqli_real_escape_string() expects exactly 2 parameters, 1 given on line 11.

 

Which is this:

 

 

 

$result = $mysqli->prepare("SELECT 1 FROM members WHERE username = " . mysqli_real_escape_string($username));

I solved it by doing 

 

..... mysqli_real_escape_string($mysqli, $username));

 

 

But It isn't finding any of the results as the code was meant to search for the same value as the one the user inputed....

 

From what you see, does the code look correct to you?

Edited December 12, 2013 by GumbiRo

[Barand]

No.

 

String values need to be in quotes.

$result = $mysqli->prepare("SELECT 1 FROM members WHERE username = '" . mysqli_real_escape_string($mysqli,$username) . "'");

Use output from $mysqli->error to find errors in your queries.

Edited December 12, 2013 by Barand

[mac_gyver]

you wouldn't use a prepared query AND put an escaped value into it. you would use a place-holder in the prepared query and bind the actual value before executing the query. if you are escaping and putting the value into the query, there's no point in going through the extra statements needed for a prepared query.

 

what is your actual code that is running this prepared query?

[GumbiRo]

you wouldn't use a prepared query AND put an escaped value into it. you would use a place-holder in the prepared query and bind the actual value before executing the query. if you are escaping and putting the value into the query, there's no point in going through the extra statements needed for a prepared query.

 

what is your actual code that is running this prepared query?

Ok, here's what happens.

 

User presses button submit on a form and here's what happens:

 

<?php
// Include database connection and functions here.
include 'db_connect.php';
include 'functions.php';


$username = $_POST['username'];


///Here is the added code just to check if the db doesn't have the same username.
$ready = false;

$result = $mysqli->prepare("SELECT 1 FROM members WHERE username = " . mysqli_real_escape_string($mysqli,$username));
if ($result && mysql_num_rows($result) > 0)

    {
        $ready = false;
    }
else
    {
    $ready = true;
    }






//I added this if ready, to check if the username isn't taken then its ready to post it

if($ready) {

///From here, the code works fine if I take the previous code.(select from...


//Working code.
// The hashed password from the form
$password = $_POST['p'];
// Create a random salt
$random_salt = hash('sha512', uniqid(mt_rand(1, mt_getrandmax()), true));
// Create salted password (Careful with the chilli)
$password = hash('sha512', $password.$random_salt);
$username = $_POST['username'];
$email = $_POST['email'];
if ($insert_stmt = $mysqli->prepare("INSERT INTO members (username, email, password, salt) VALUES (?, ?, ?, ?)"))
{
$insert_stmt->bind_param('ssss', $username, $email, $password, $random_salt);
// Execute the prepared query.
$insert_stmt->execute();
header("Location: '..\..\..\?success=1'");

}
else
{
header("Location: '..\..\..\?registrationfailed=1'");
}

///Added this else just to check if ready is false.
}else {

header("Location: '..\..\..\?registrationfailed=2'");
}


?>

Here's the complete code of the file.

There's some comments explaining what I want to accomplish! Thank you for your time!

Edited December 12, 2013 by GumbiRo

[mac_gyver]

sorry to be blunt, but you need to add an item 0 to your list of steps -

 

Steps:

0. pick the database library you intend to use and learn how to make a connection, form and execuite a query, check if the query ran, and retrieve the result from the query and if using prepared queries, how to form and prepare the query, bind any inputs, execute the query, bind any result, and retrieve the result from the query.
1.check user input.
2.Check database for duplicate.
3.Return false/true.

 

before you can write code that uses a database for your data, you must learn how to use the database statements at all. this thread is just randomly trying things and that is a waste of time when trying to program.

 

leave prepared queries for later, just get a normal mysqli query to work, escaping input values as needed.

 

here are some things about the code in general that need to be changed/fixed -

 

1) your form processing code needs to test if a form was submitted at all. you should not run the posted code unless you know the form has been submitted.

 

2) you need to validate the expected form data to insure that it is at least not empty. there's no point in trying to use empty data as that just wastes resources running queries that won't match anything. your current code would happily insert empty data into your database.

 

3) code that combines conditions like this - if ($result && mysql_num_rows($result) > 0) doesn't do what you think. if the query is failing due to an error, this combined test will say that the username is not in use, when in fact it could be in use. you must first test for query errors, and handle them separately, before you can use the result from a query. P.S. for this specific line of code, you would need to use the mysqli form of num_rows.

Edited December 12, 2013 by mac_gyver

[GumbiRo]

Ok, I took your advice and headed to the php archives.

From what I can tell. These could be a simpler way of doing it but Im getting it wrong.


 

 //Query the DB
  $sql = mysqli_query("SELECT 1 FROM members WHERE username = " . mysqli_real_escape_string($mysqli,$username));

if(!$result = $mysqli->query($sql)){
    die('There was an error running the query [' . $db->error . ']');
}
 
if (mysqli_num_rows($result) > 0)
    {
        $ready = false;
    } else {
    
    $ready = true;
    }

But now Im getting this error:


 Warning: mysqli_query() expects at least 2 parameters, 1 given...

Which I believe leads to :

Warning: mysqli::query() [mysqli.query]: Empty query

So...which would be the second parameter?

[GumbiRo]

Bare with me, Im auto-learning because no one around here knows about php....So Im trying to do this by the books I find and online resources.

:/ thats why I come to you guys!

[Barand]

$sql should just define the string.

$sql = "SELECT 1 FROM members WHERE username = '" . mysqli_real_escape_string($mysqli,$username) . "'";

Note that the string value '$username' needs to be inside quotes in a query.

 

You also need to pass the mysqli connection as the first parameter in mysqli_query()

[GumbiRo]

$sql should just define the string.

$sql = "SELECT 1 FROM members WHERE username = '" . mysqli_real_escape_string($mysqli,$username) . "'";

Note that the string value '$username' needs to be inside quotes in a query.

 

You also need to pass the mysqli connection as the first parameter in mysqli_query()

Thank you good sir!

 

So the final part of the code would be like this?

 

 

 

 $sql = "SELECT 1 FROM members WHERE username = '" . mysqli_real_escape_string($mysqli,$username) . "'";
     
     if(!$result = $mysqli->query($sql)){
    die('There was an error running the query [' . $db->error . ']');
}
while($row = $result->fetch_assoc()){
   $counter = $counter + 1;
}
if($result->num_rows > 0 ) {

    $ready = false;
    } else {
    
    $ready = true;
    }

I know im a pain, but for now this code seems to be working. Just to check if the user inputed an existent username.

 

Would you find this as sloppy code? If so, what changes would you make?

 

Im not asking you just to say for me to change it, but to use it in the future. What weaknesses would you believe the code has?

 

Thank you very much for your time and patience(everyone)!

[Barand]

It seems like all you are interested in knowing is "how many records are there?".

 

The most inefficient way to do this is fetch all the records then count them. To make  it worse you are counting them all twice

• you loop through the records and set $counter to the record count
• you then call num_rows to get the count

$sql = "SELECT COUNT(*) as count
    FROM members
    WHERE username = '" . $mysqli->real_escape_string($username) . "'";
     
if(!$result = $mysqli->query($sql)){
    die('There was an error running the query [' . $db->error . ']');
}
list($count) = $result->fetch-row();
$ready = $count==0;