Jump to content


Photo

Quick question. - Accessing Database with MySQL and PHP

mysql php html

  • Please log in to reply
15 replies to this topic

#1 GumbiRo

GumbiRo

    Member

  • Members
  • PipPip
  • 19 posts

Posted 11 December 2013 - 06:35 PM

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 by GumbiRo, 11 December 2013 - 06:44 PM.


#2 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,134 posts
  • LocationCheshire, UK

Posted 11 December 2013 - 06:47 PM

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


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#3 GumbiRo

GumbiRo

    Member

  • Members
  • PipPip
  • 19 posts

Posted 11 December 2013 - 06:52 PM

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 by GumbiRo, 11 December 2013 - 06:53 PM.


#4 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,134 posts
  • LocationCheshire, UK

Posted 11 December 2013 - 07:01 PM

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.


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#5 GumbiRo

GumbiRo

    Member

  • Members
  • PipPip
  • 19 posts

Posted 11 December 2013 - 07:22 PM

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 by GumbiRo, 11 December 2013 - 07:23 PM.


#6 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,134 posts
  • LocationCheshire, UK

Posted 11 December 2013 - 07:30 PM

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


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#7 GumbiRo

GumbiRo

    Member

  • Members
  • PipPip
  • 19 posts

Posted 11 December 2013 - 09:30 PM

 

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 by GumbiRo, 11 December 2013 - 09:40 PM.


#8 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,134 posts
  • LocationCheshire, UK

Posted 12 December 2013 - 02:52 AM

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 by Barand, 12 December 2013 - 02:53 AM.

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#9 mac_gyver

mac_gyver

    Advanced Member

  • Administrators
  • 2,259 posts

Posted 12 December 2013 - 07:00 AM

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?


multi-purpose programming fool and resident naysayer [We try not be negative in replies, but telling someone what they're doing wrong, while staying politically correct, isn't always going to happen.]

#10 GumbiRo

GumbiRo

    Member

  • Members
  • PipPip
  • 19 posts

Posted 12 December 2013 - 03:55 PM

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 by GumbiRo, 12 December 2013 - 04:02 PM.


#11 mac_gyver

mac_gyver

    Advanced Member

  • Administrators
  • 2,259 posts

Posted 12 December 2013 - 04:58 PM

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 by mac_gyver, 12 December 2013 - 05:01 PM.

multi-purpose programming fool and resident naysayer [We try not be negative in replies, but telling someone what they're doing wrong, while staying politically correct, isn't always going to happen.]

#12 GumbiRo

GumbiRo

    Member

  • Members
  • PipPip
  • 19 posts

Posted 12 December 2013 - 06:13 PM

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?



#13 GumbiRo

GumbiRo

    Member

  • Members
  • PipPip
  • 19 posts

Posted 12 December 2013 - 06:35 PM

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!



#14 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,134 posts
  • LocationCheshire, UK

Posted 12 December 2013 - 06:47 PM

$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()


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#15 GumbiRo

GumbiRo

    Member

  • Members
  • PipPip
  • 19 posts

Posted 12 December 2013 - 07:11 PM

$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)!



#16 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,134 posts
  • LocationCheshire, UK

Posted 13 December 2013 - 05:06 AM

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;

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com