Jump to content
Larry Ullman's Book Forums
Matt

Frustrating Mysql Error!

Recommended Posts

Larry,

 

I have made great progress working on the "Art Gallery" site I had posted about a few weeks ago!

 

A Site With Subdirectories For Each User!

 

I have the user registering on the main index page, then they are directed to a page where they create a gallery/profile (they fill out a form where they input their name, gallery name, name of the directory that will be created, upload a profile pic, etc...). I have the php set up to create the gallery directory and drop an index file into it and it works perfectly.

 

The problem I am having is that I am trying to develop a login system where the artist can login to his/her own gallery. This should seem simple enough as I am using php code that is virtually identical to yours from Example 1. There is a login box at the top of the gallery index page, but when the artist tries to log in I get the following error:

 

An error occurred in script '/home/tueslcom/mysqli_ecommerce_connect.php' on line 47:

mysqli_real_escape_string() expects parameter 1 to be mysqli, null given

 

The offending line of code is from the mysqli_connect.php script:

 

return mysqli_real_escape_string ($dbc, hash_hmac('sha256', $password, 'c#haRl891', true));

 

I spent 3 hours last night going over all the code, and like I said, it is the same as yours Larry. I have just changed the names of the files (i.e. gallery_login_form.inc.php, gallery_login.inc.php. etc...) The only big difference I can think of is that in the "index.php" page I am calling a function in the includes directory which actually creates the page itself. This was done so as to minimize the amount of code in the index file since it was being created on the fly! However, I can't see how this would be a problem. In fact, if you just view the index page in a gallery directory, it is making a database query when the page loads in order to display the profile info.!

 

You can see the page here:

Example Gallery Page

 

I did a google search on the error and even found a link back to your old forum from 2006. It might have something to do with calling "mysqli_real_escape_string" or the database connection (i.e. $dbc) before the connection has actually been set, but that is impossible! I am requiring the mysqli_connection script in almost the first line of the code which creates the page!

 

I have been pulling my hair out on this one, but I'm almost sure the answer involves changing one line of code!

 

This functionality is pretty important to the site, so any help would be greatly appreciated!

 

Thanks in advance!

 

Matt

Share this post


Link to post
Share on other sites

My guess would be that either (for some reason) the DB connection has not yet been established, or the established DB connection stored in $dbc is not right; probably an incorrect argument in the mysqli_connect function. Are you sure you DB connection is working properly elsewhere?

 

Sorry for the dumb comments, but I can't imagine anything else without seeing the actual code, but I also can't imagine you would want to show your DB credentials to us.

  • Upvote 1

Share this post


Link to post
Share on other sites

In your get_password_hash function are you defining $dbc as global?

 

global $dbc;

 

I updated a project of mine from mysql to mysqli and I got a very similar error to what you are getting. I needed to switch the position of $dbc so that the mysqli_connect call came first. But in your error it shows that it is also NULL which to me tells me that there is NO mysqli database connection call contained inside of $dbc. So is $dbc created correctly AND is $dbc being referenced from this function of yours so it is being seen?

 

From mysql.inc.php file from EX1.

 

$dbc = mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

 

AND inside both the functions in this file

$dbc is being called by global variable OR if you don't I can see you getting that error.

 

- T

  • Upvote 1

Share this post


Link to post
Share on other sites

Maybe the include file that opens the database is not being included because wrong directory specified because index is in subdirectory.

Share this post


Link to post
Share on other sites

HartleySan and I have most likely found the source of the problem! It has to do with $dbc losing scope within the get_password_hash() function!

 

In your get_password_hash function are you defining $dbc as global?

global $dbc;

Yes, $dbc was set as global within the functions, but as it turns out, that didn't matter!

 

So is $dbc created correctly AND is $dbc being referenced from this function of yours so it is being seen?

Terry, you hit the nail right on the head!

Last night, HartleySan and I stayed up trying to figure this out over the phone. We tried out several things and finally honed in on the problem.

 

After a bit of troubleshooting, we then tried pasting the following into the get_password_hash() function, and to our surprise the login worked fine:

 

$dbc = mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

What this told us was that the mysqli_connect.php script was being accessed, but that the $dbc variable had somehow lost scope when it came to the get_password_hash() function. It doesn't do this when using Larry's Example1 site, so I thought that it must have been caused by something I was doing differently with my site.

 

I did some research today on php.net and found out that variables established in php lose scope when used inside a function (as you probably already know), which is quite the opposite of C/C++. Furthermore, if a variable is declared inside a function (which is true in our case since we are importing the database connect file inside of the create_gallery() function), then then somehow the scope of $dbc is lost within all functions using it, even if they are declaring it as global. When I declared $dbc as global at the top of the mysqli_connect.php script...

 

global $dbc;
$dbc = mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

...it again worked fine!

 

Although it now makes some sense, it is still somewhat strange behavior!

Was this similar to what you did Terry?

I'd like to hear Larry's feedback on this as well!

 

Matt

Share this post


Link to post
Share on other sites

This code example shows what is going on in my case:

 

Please note for using global variable in child functions: 

This won't work correctly... 

<?php 
function foo(){ 
   $f_a = 'a'; 

   function bar(){ 
       global $f_a; 
       echo '"f_a" in BAR is: ' . $f_a . '<br />';  // doesn't work, var is empty! 
   } 

   bar(); 
   echo '"f_a" in FOO is: ' . $f_a . '<br />'; 
} 
?> 

This will... 

<?php 
function foo(){ 
   global $f_a;   // <- Notice to this 
   $f_a = 'a'; 

   function bar(){ 
       global $f_a; 
       echo '"f_a" in BAR is: ' . $f_a . '<br />';  // work!, var is 'a' 
   } 

   bar(); 
   echo '"f_a" in FOO is: ' . $f_a . '<br />'; 
} 
?>

Taken from php.net

Share this post


Link to post
Share on other sites

What I discovered when switching from mysql to mysqli is that when you use mysqli_connect it handles selecting database for you as seen with the last parameter DB_NAME.

 

$dbc = mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

 

With the old style mysql_connect you have to add a separate call to select the database.

 

$dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD);
$dbSelect = mysql_select_db(DB_NAME, $dbc);

 

 

I have never seen a function defined within a function before. Not sure why you would want to do that.

 

With PHP all variables defined within a function are local variables UNLESS specifically stated as global which then allows that variable to be used outside the function. Depending on how you do this it can be a security issue and shouldn't be a regular coding practice.

 

Functions are isolated modules, a room with no windows or doors so it can't know what is outside of itself UNLESS you tell it.

Declaring a variable as global within a function connects that variable to global variable with same name outside of itself OR creates a new global variable if one doesn't exist.

 

For example:

 

Global $seeMe;    <-- New global variable
$seeMe = (string)"I am a String Variable";

Function my_func($hi){

echo $seeMe;   <-- variable used within function, isolated variable and value is empty.

}

 

As you found out $seeMe is not accessible within a function because it has no clue about anything outside of itself UNLESS you tell it which in the above example I did not.

 

 

Global $seeMe;    <-- New global variable
$seeMe = (string)"I am a String Variable";

Function my_func($hi){
Global $seeMe;  <-- NOW tells the function of the existence of a new variable which is GLOBAL defined and in this case there is an existing variable in the 
GLOBAL scope so my_func() now knows about it and can use it.

echo $seeMe;   <-- GLOBAL variable used within function and value is "I am a String Variable";

}

 

 

In your code you have a function within a function and neither one knows anything about the other one.

 

Instead of declaring functions inside of each other I create a separate file with all my functions and then just call the function I need within my code.

 

If there is only one value as a result of the functions operation you can set that return value as a Global Variable BUT it is safer to specifically return the result to the specific code that called the function and not have some GLOBAL variable floating around out there. Comes down to personal preference and can save on coding but still is tighter to specifically lay down a road for each variable, section of code and force it to follow that paved path then have free range code and variables floating around all over. Depends on complexity of the program but having paved paths or roads for your code can help with debugging too.

Share this post


Link to post
Share on other sites

Reading through the code example you posted again, I have a tip for you that I discovered when debugging my current project.

 

<?php 

   function bar(){ 
       $f_a = 'a'; 
       echo '"f_a" in BAR is: ' . $f_a . '<br />';  
   } 

   bar(); //  <-- example on how to call the above function

?>

 

 

I simplified the code to make it easier to describe the issue.

 

In the above example the function outputs a string within the function itself, with $f_a being dynamically added and the value is "a".

 

Lets assume that you are creating a dynamic page and calling this function to auto propigate a section of a page and this is to be inserted between a DIV tag formatted by CSS.

 


<div class="class_1">

<?php  bar(); ?>

</div>

 

 

You would assume that the ECHO of the function would fall within the DIV correct?

Will you would be wrong. Why?

 

In the layout of the function code the echo is not specifically being returned instead it is just outputted to the browser. It comes down to order of operation and what I was talking about above with functions not knowing anything about what is outside of itself.

 

I may not be explaining this too well but I will try to layout my point below.

 

 

1: You have a function that echo's a string to the browser

 

2: You have a HTML page formatted by CSS and purpose is to format the string echoe'd from the function

 

 

When you specifically RETURN a value you are essentially saying go back to the exact place where the function was called.

So in the above example it would be go back to the specific place where bar(); is located. Like bar() is a bookmark or place holder.

 

BUT when you do not do a specific RETURN from a function and in the specific example above I did not you are saying, output the string to the browser NOW.

The problem is you aren't telling it where to output the string so it just does it as if it were a separate page. The browser see's the new outputted string and it sees the CSS formatted sections of the page but the new string isn't related to the formatted section so it just places it at the top somewhere.

 

Now depending on your CSS and the HTML in the page you will get weird result and a broken template. The string may not be displayed at the top of the page it could be anywhere on the page depending on the browser.

 

This is why I say you should return specifically any data you want back out from a function. Though global variables are different and have nothing to do with display I still think it is a good idea and this is maybe a poor example but it is one specific example of why I think so.

 

To actually get the above example to work as expected you would want to do something like this with the function:

 

 

<?php 

   function bar(){ 
       $f_a = 'a'; 
       $new_string = '"f_a" in BAR is: ' . $f_a . '<br />';  
       Return $new_string;
   } 

?>

 

 

Then in the page do this:

 

 

 


<div class="class_1">

<?php  echo bar(); ?>

</div>

Share this post


Link to post
Share on other sites

I have never seen a function defined within a function before. Not sure why you would want to do that.

 

I'll try to explain. Do you want the short version or the long version? I'll try the short version.

 

What I'm doing is allowing a user who registers with my site to create his/her own art gallery. Once they register, they then fill out a form with various information ("gallery name", "directory name", "gallery description", etc...). As soon as this form is submitted, a folder is instantly created with the directory name they provided in the form. An index file is created on the fly and dropped into this folder as well. What is essentially happening is they are creating a site within my site!

 

The index file is very simple. It first requires a file called gallery_functions.inc.php from the includes directory. Next, it grabs the directory name it resides in and then calls a function, create_gallery() in the gallery_functions.inc.php file and passes the directory name as an argument.

 

Here is the create_gallery() function:

function create_gallery_index ($directory)
{

// Require the configuration before any PHP code as the configuration controls error reporting:
require('../../includes/config.inc.php');

// The config file also starts the session.

// Require the database connection:
require (BASE_URI . 'mysqli_ecommerce_connect.php');

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
include ('../../includes/gallery_login.inc.php');
}

// Include the header file:
include ('../../includes/gallery_header.html');

echo '<div id="content">';

/* PAGE CONTENT STARTS HERE! */
$q = "SELECT user_id, first_name, last_name, image, gallery_name, description FROM profiles WHERE directory_name='$directory'";
$r = mysqli_query ($dbc, $q);

// Get the number of rows returned:
$rows = mysqli_num_rows($r);

if ($rows == 1)
{
$row = mysqli_fetch_array($r, MYSQLI_ASSOC);
//echo '<img src="' . BASE_URL . 'includes/image_proxy.php?img=' . $row['image'] . '" alt=""/>';
echo "<p><h2>{$row['gallery_name']}</h2></p\n";
echo '<img src="../../includes/image_proxy.php?img=' . $row['image'] . '" height="200" width="200" alt=""/>';
echo "<p>{$row['first_name']}" . " {$row['last_name']}</p>\n";
echo "<p>{$row['description']}</p>\n";
}
else
{
echo "<p>There was an error!</p>\n";
}


echo "</div>";

echo '        <div class="sidebar">';
                  ....blah, blah, blah        
echo '         </div>';

echo '<div class="clear"></div>';

include ('../../includes/gallery_footer.html');


// Include the footer file to complete the template:
//require ('../../includes/footer.html');

}

 

I've removed some of the echo statements dealing with content, but you can get the basic idea. Since the mysqli_connect.php scrip It is being required within the create_gallery() function, the global definition of $dbc within the $get_password_hash() function inside the mysqli_connect file is being lost!

 

Aside from that one problem, the set-up works quite nicely!

 

Functions are isolated modules, a room with no windows or doors so it can't know what is outside of itself UNLESS you tell it.

Declaring a variable as global within a function connects that variable to global variable with same name outside of itself OR creates a new global variable if one doesn't exist.

 

Yes. This behavior is interesting, and quite unlike Java, VB, C based languages, etc...

Share this post


Link to post
Share on other sites

Yes. This behavior is interesting, and quite unlike Java, VB, C based languages, etc...

 

Like I said I have not seen that done in php before but if you got it all to work how you need it to then great.

 

I do not know if there would be a time savings enclosing a function within a function you happen to call it from, sort of a private function, but if there is I can't see it being very much versus being included from a separate file, the call to the function itself is no different.

 

Glad you got it all working.

Share this post


Link to post
Share on other sites

Well now that I have that working, there are a few different ways to implement it.

 

Since the mysqli_connect file is being used both within a function (in the case of the galleries) and outside (in the case of the top level index page), both situations need to be handled by the database connection script.

 

I have found 3 possibilities that all seem to work, however, I wanted to know which is the best, most secure way to implement it!

 

A - Declare the $dbc variable as global at the top of the mysqli_connect file:

<?php

// This file contains the database access information. 
// This file establishes a connection to MySQL and selects the database.
// This file defines a function for making data safe to use in queries.
// This file defines a function for hashing passwords.

// Set the database access information as constants:
DEFINE ('DB_USER', 'tueslcom_matt');
DEFINE ('DB_PASSWORD', 'Japan2004');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'tueslcom_gallery');

// Make the connection:
global $dbc;
$dbc = mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

...

 

B - Redefine the $dbc variable in the get_password_hash() function:

function get_password_hash($password) {

// Need the database connection:
       $dbc = mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

// Return the escaped password:
return mysqli_real_escape_string ($dbc, hash_hmac('sha256', $password, 'c#haRl891', true));

}

 

C - Create a second mysqli_connect script for the galleries that has the $dbc variable declared as global at the top (as per the first example). This would limit any security risk only to situations where the gallery pages are accessing the database. This seems a bit redundant, however, since the one script should be able to be written to handle both access scenarios.

 

A fourth option might even be to define the $dbc variable as is at the top of the script and then use a getDbc() function to pass the variable to whatever other function might need it:

<?php
function getDbc(){
  global $dbc;
  return this->$dbc;
}

 

I'm not too sure how well that would work, but I was just running over several ideas and this one seemed like it might be possible to implement. I'm just hoping there is a more straightforward way to do this that's secure.

 

Is it bad to define the $dbc variable as global at the top of the script? If so, why? Nobody can access the script from outside because it is above the web directory. I would think that example 'B' would be the best, but I'm not 100% confident.

 

Larry, what do you think?

Share this post


Link to post
Share on other sites

None of the above. As for B, you definitely don't want to create a new database connection with every function call. With C, you don't want to create a second connection script, as the defeats part of the purpose of having globally-useful scripts. With D (the fourth option), $this is only meaningful if you're using OOP. I'm just skimming all this text, but I don't think you're using OOP.

 

There are really two best, easy approaches. One is to make the $dbc variable global within each function as needed, as I do in the book. The other, which I guess is actually better, is to pass the database connection to the function that needs it. Perhaps I've missed something because I'm seeing the answer as very simple and I haven't take the time to read this entire thread in detail. Let me know if that's the case.

Share this post


Link to post
Share on other sites

Larry,

 

Thank you so much for your help! The answers you gave were just what I wanted to hear - well not really, but you know what I mean ;) The problem is a bit out of the ordinary, so one of the solutions I posted (or a better one which I hadn' thought of) is absolutely necessary for the database connection to work right. I know you are busy, but you might want to look back in the thread a bit just to see what's going on here.

 

Here`s a recap:

What I'm doing is allowing a user who registers with my site to create his/her own art gallery. Once they register, they then fill out a form with various information ("gallery name", "directory name", "gallery description", etc...). As soon as this form is submitted, a folder is instantly created with the directory name they provided in the form. An index file is created on the fly and dropped into this folder as well. What is essentially happening is they are creating a site within my site!

 

The index file is very simple. It first requires a file called gallery_functions.inc.php from the includes directory. Next, it grabs the directory name it resides in and then calls a function, create_gallery() in the gallery_functions.inc.php file and passes the directory name as an argument."

 

The mysqli_connect.php script is being required within the create_gallery() function, and because of this, the global definition of $dbc within the get_password_hash() function inside the mysqli_connect file is being lost! Therefore, the script generates an error within the get_password_hash() function!

 

The mysql_connect script is being used in 2 different scenarios:

1 - It is being included within a function - in the case of the individual galleries (as mentioned above)

2 - It is being included in the normal way (outside of a function) in the case of a user just going to the top level of the site and logging in through the main index.php page

 

The problem is getting the script to handle both scenarios without the $dbc variable losing global scope/the functions internal to the mysqli_connect script losing access to the $dbc variable.

 

HartleySan and I have both looked at this and are really at a loss as to the correct way to implement it. Although we have gotten the script to work using "B" above, we know it's not the best way to do this, as you said! This is a real conundrum Larry!

 

What else can we do to allow global access to $dbc?

Share this post


Link to post
Share on other sites

Larry, not to butt in, but actually, if you don't mind, I think Matt and I can figure this one out. I haven't had enough time to look at his code personally, and the truth is, it's rather complex, and not easy to explain on this forum. As such, please give us a little more time to look into it first.

 

Hopefully, we can find a good answer, and post it here.

 

Matt, sorry for that. We'll find a good solution though. Please be patient.

Share this post


Link to post
Share on other sites

I don't mind at all. Matt's last request was for me to actually read what you're talking about. Like I have time to read questions before I answer them! Do what you want to do, feel free to tell me not to answer questions all you want, and just send out an S.O.S. when you'd like me to chime in (on this particular thread).

 

 

Good luck!

Share this post


Link to post
Share on other sites

Larry, not to butt in, but actually, if you don't mind, I think Matt and I can figure this one out. I haven't had enough time to look at his code personally, and the truth is, it's rather complex, and not easy to explain on this forum. As such, please give us a little more time to look into it first.

 

Hopefully, we can find a good answer, and post it here.

 

Matt, sorry for that. We'll find a good solution though. Please be patient.

 

 

Jon,

 

Are you insane? Larry was about to answer!

Share this post


Link to post
Share on other sites

Hehe! Well, I guarantee Matt does, but I'm going to refuse on both of our behalves for the time being.

 

The fact of the matter is that we have the site working, albeit not in the best fashion. In the meantime, Matt's gonna continue developing the site, and when I get the time (which is hard to find these days), I'm gonna look very carefully at his code, study more about global in PHP, and try to find the best solution.

 

We'll get back with you, Larry. As always, we appreciate all the help and support, but please give us a bit longer. Thanks.

Share this post


Link to post
Share on other sites

So...do you want an answer from me (at this time) or no? Up to you...

 

Larry,

 

I would have to say yes! HartleySan and I can probably figure it out, but since a bad decision could jeopardize database security, I would like to hear what you think would be the best solution.

 

Also, why is it that declaring the $dbc variable as global at the top of the mysqli_connect script leads to a security risk? First of all, nobody even knows the name of the variable. Second, how can they access this script if it is outside the web root?

 

Thanks a lot!

 

Matt

Share this post


Link to post
Share on other sites

It took me a while (sorry, was a bit slow-headed about this one), but I finally realized what was going on. It's exactly as Matt stated in his example from PHP.net above.

 

As for a solution, well, I think there are many, but I'm more and more leaning toward an entire restructing of Matt's site (much to his chagrin). Certainly, Larry, if you want to share your opinions, we'd like to hear them, but I'm realizing more and more that this issue (among many other potential ones) can be easily resolved but simplifying and restructing Matt's site a bit.

 

I'll talk with him more later, and see what he thinks. In the meantime, please share your opinions, Larry. Thanks.

Share this post


Link to post
Share on other sites

Sorry for the delay. I was out of town for the long (US) holiday weekend. I've read this entire thread again, in sufficient detail, I think, and I'm still standing by my previous answer. if you need to use a database connection, or any variable, inside of a function, the preferred way is to pass it as an argument to that function. That being said, I kind of don't like what you're doing with your create_gallery_index() function. To me it's way too much logic within a function call. Moreover, you're including the configuration file, which I would think the script that calls the function would have also included, and you're including the MySQL connection script, which I would again think that the parent script would have included. You're also doing a lot of HTML within the function, which can be a red flag.

 

My inclination, then, would be to rethink the logic. I would be inclined to either make this functionality an includable script, without its function definition and call (i.e., you include the script that creates the gallery when it's necessary) or you create a separate script that does what this function does: the user clicks this to create a gallery, the gallery script runs (without the user seeing it) and the script redirects the user back to another page, passing along a flag variable (indicating success of the operation) in the URL.

 

That's my thinking. Let me know if this doesn't help or if I missed something or whatever.

Share this post


Link to post
Share on other sites

All good thoughts, Larry. Thanks for sharing them. I haven't spoken with Matt for a while about it, so I will see what's going on.

 

Also, I like your idea of passing the DB connection as an argument. Makes sense.

Share this post


Link to post
Share on other sites

Larry,

 

Thank you so much for your good suggestions!

 

My inclination, then, would be to rethink the logic. I would be inclined to either make this functionality an includable script, without its function definition and call (i.e., you include the script that creates the gallery when it's necessary)

When I fist saw this, I thought "Damnit! Larry's throwing a wrench into our great design!" (Larry, you know how I am). However, after rereading the post, I realized that Larry is 100% right! Why are we using a function to create the page at runtime, when an included script can do the same thing without all the database connection headaches!

 

Then I talked to HartleySan and he brought up a good point! The index page that creates a gallery is very simple. It contains just an include for the gallery_functions.inc.php file and a call to the create_gallery() funtion, passing along the folder name (i.e. the gallery name) that the index file resides in as an argument. We don't want any content embedded within it. HartleySan thinks that calling a function which creates the galley content is better than including a file (which does the same) because if we ever decide to change the content later, then we only have to do it in one place (i.e. inside the function). The function is like a "black box" from the perspective of the index file calling it, and all the work is done there. I suppose that the same could be said of an included file, but he just felt that doing this in a function was better.

 

What are your thoughts on this Larry?

 

or you create a separate script that does what this function does: the user clicks this to create a gallery, the gallery script runs (without the user seeing it) and the script redirects the user back to another page, passing along a flag variable (indicating success of the operation) in the URL.

I'm not sure I fully understand what you mean by this Larry. Could you be a little more specific?

 

Thanks again, Larry!

 

Matt

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...