Search working on live website but not on WPEngine

[walleyeguy13]

Greetings...

 

We are migrating our Wordpress website to WPEngine.  It is currently sitting on a local Mac server.  The following script for our Staff Directory works fine on the local system.  It is simply inserted into a Wordpress page with results posted to another Wordpress page.   However, for some odd reason the very same script is not working on WPEngine (temp location).   The intial page displays all of the entries from the Staff database, but when I enter a name in the Search field and submit I get no results on the submission page.

 

So, going to http://bemidji.wpengine.com/staff/staff-directory/ should display all names from the Staff database.  Code:

require 'dbconfig.php';

$con = mysqli_connect($db_host, $db_user, $db_password, 'staff') or die (mysql_error());

echo '<form action="http://bemidji.wpengine.com/staff/staff-directory/staffdirectorysearch/" method="post">';
echo '<strong>Search For: </br></strong><input type="text" name="Search" placeholder="Last Name/First Name Search" /></br>';
echo '<strong>Sort By: </strong><select name="Sort">
<option value="Fname">First Name</option>
<option value="Lname">Last Name</option>
<option value="Position">Position</option>
<option value="Location">Location</option>
</select>';

echo '<input type="submit" value="Search" />';
echo '</form>';

$sql = "SELECT * FROM staffdata ORDER BY LName ASC";

	
$mydata = mysqli_query($con,$sql);

echo '<table id="staffdirectory" class="tablepress" >
<thead>
    <tr>
        <th scope="col">First Name</th>
        <th scope="col">Last Name</th>
        <th scope="col">Position</th>
        <th width=120px scope="col">Location</th>
	<th width=60px scope="col">Phone</th>
        <th scope="col">Ext.</th>
	<th scope="col">Email</th>
    </tr>
</thead>

<tbody class="row-hover" role="alert" aria-live="polite" aria-relevant="all">';

    

while ($record = mysqli_fetch_array($mydata)) {
	
$count++;
	    if( $count % 2 == 0 ) {
            $class = " class='odd'";
        } else {
            $class = "class='even'";
        }

        echo '<tr' . $class . '><td class="column-1">'; 
        echo $record['FName'];
        echo '</td><td class="column-2">'; 
        echo $record['LName'];
        echo '</td><td class="column-3">'; 
        echo $record['Position'];
        echo '</td><td width=120px class="column-4">'; 
        echo $record['Location'];
        echo '</td><td width=60px class="column-5">';
        echo $record['Phone'];
        echo '</td><td class="column-6">'; 
		echo $record['Ext'];
        echo '</td><td class="column-7">'; 
		echo $record['Email'];
        echo '</td></tr>';
	}
	
	echo '</tbody></table>';

As you can see, the form action is to post to bemidji.wpengine.com/staff/staff-directory/staffdirectorysearch and the code on that page is:

require 'dbconfig.php';

$con = mysqli_connect($db_host, $db_user, $db_password, 'staff') or die (mysql_error());

echo '<form action="http://bemidji.wpengine.com/staff/staff-directory/staffdirectorysearch/" method="post">';
echo '<strong>Search For: </br></strong><input type="text" name="Search" placeholder="Last Name/First Name Search" /></br>';
echo '<strong>Sort By: </strong><select name="Sort">
<option value="Fname">First Name</option>
<option value="Lname">Last Name</option>
<option value="Position">Position</option>
<option value="Location">Location</option>
</select>';

echo '<input type="submit" value="Search" />';
echo '</form>';
echo '<a href="http://bemidji.wpengine.com/staff/staff-directory/">View All</a>';

if (isset($_POST['Search'])) {
$searchq = $_POST['Search'];
$sortq = mysql_real_escape_string($_POST['Sort']);
echo '</br>Sorted By: ' . $sortq;
$sql = "SELECT * FROM staffdata WHERE LName LIKE '%".$searchq."%' OR FName LIKE '%".$searchq."%' ORDER BY `$sortq` ASC";
}

$mydata = mysqli_query($con,$sql);

echo '<table id="staffdirectory" class="tablepress" >
<thead>
    <tr>
        <th scope="col">First Name</th>
        <th scope="col">Last Name</th>
        <th scope="col">Position</th>
        <th width=120px scope="col">Location</th>
	<th width=60px scope="col">Phone</th>
        <th scope="col">Ext.</th>
	<th scope="col">Email</th>
    </tr>
</thead>

<tbody class="row-hover" role="alert" aria-live="polite" aria-relevant="all">';

while ($record = mysqli_fetch_array($mydata)) {

$count++;
if( $count % 2 == 0 ) {
$class = " class='odd'";
} else {
$class = "class='even'";
}

echo '<tr' . $class . '><td class="column-1">'; 
        echo $record['FName'];
        echo '</td><td class="column-2">'; 
        echo $record['LName'];
        echo '</td><td class="column-3">'; 
        echo $record['Position'];
        echo '</td><td width=120px class="column-4">'; 
        echo $record['Location'];
        echo '</td><td width=60px class="column-5">';
        echo $record['Phone'];
        echo '</td><td class="column-6">'; 
		echo $record['Ext'];
        echo '</td><td class="column-7">'; 
		echo $record['Email'];
        echo '</td></tr>';
	}
	
	echo '</tbody></table>';

mysqli_close($con);

So if you type in a name from the staff database and hit Search you get nothing.    The properly working version can be seen here:

 

www.bemidji.k12.mn.us/staff/staff-directory

 

Any help figuring out what I am missing is appreciated.

[mac_gyver]

when you update old mysql_ based code, you must convert all the statements and when debugging php problems, get php to help you by setting error_reporting to E_ALL and display_errors to ON.

 

you missed a mysql_real_escape_string (and a few mysql_error()) statements that would be throwing php errors to help pin down the problem.

 

the reason this works on one system and not another is because php and the all in one system creators thought it would be funny to set up default database connection credentials and to have mysql_ statements try to make a connection when there isn't one. on the system where this doesn't work, there are probably no default connection credentials, so the mysql_real_escape_string function call can't make a connection and it returns a null/false value instead of the escaped search term.

[walleyeguy13]

Thank you for the response.

[walleyeguy13]

After adding the error reporting (to the second script above), this is the error it is throwing:

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /nas/content/live/bemidji/wp-content/plugins/insert-php/insert_php.php(48) : eval()’d code on line 49

Edited December 15, 2016 by walleyeguy13

[walleyeguy13]

OK...  The above error was caused by $sql ... After removing the $sortq variable and changing the line to:

$sql = "SELECT * FROM staffdata WHERE LName LIKE '%".$searchq."%' OR FName LIKE '%".$searchq."%' ORDER BY LName ASC";

So that I just sorted by LName I am now getting the expected output.

[benanamen]

Now you need to update your code to use prepared statements. You NEVER EVER send user supplied data directly to the database. Your code is vulnerable to an SQL Injection Attack.

[walleyeguy13]

Now you need to update your code to use prepared statements. You NEVER EVER send user supplied data directly to the database. Your code is vulnerable to an SQL Injection Attack.

Would you be willing to offer an example?   I've done a bit of searching and am not entirely sure how to start in this case.

[benanamen]

Here you go!

[walleyeguy13]

Ah yes... Clever.   I'll be more specific:

error_reporting(E_ALL);
ini_set('display_errors', 1);

require 'dbconfig.php';

$con = mysqli_connect($db_host, $db_user, $db_password, 'staff') or die (mysql_error());

echo '<form action="http://bemidji.wpengine.com/staff/staff-directory/staffdirectorysearch/" method="post">';
echo '<strong>Search For: </br></strong><input type="text" name="Search" placeholder="Last Name/First Name Search" /></br>';
// echo '<strong>Sort By: </strong><select name="Sort">
// <option value="Fname">First Name</option>
// <option value="Lname">Last Name</option>
// <option value="Position">Position</option>
// <option value="Location">Location</option>
// </select>';

echo '<input type="submit" value="Search" />';
echo '</form>';
echo '<a href="http://bemidji.wpengine.com/staff/staff-directory/">View All</a>';

if (isset($_POST['Search'])) {
$searchq = $_POST['Search'];
// $sortq = "Last Name";
// $sortq = $_POST['Sort']);
// echo '</br>Sorted By: ' . $sortq;


}
$stmt = mysqli_prepare($con,"SELECT * FROM staffdata WHERE LName LIKE ? OR FName LIKE ? ORDER BY LName ASC") 
mysqli_stmt_bind_param($stmt, "ss", $searchq , $searchq);
mysqli_stmt_execute($stmt);
	$mydata = get_result($stmt);

echo '<table id="staffdirectory" class="tablepress" >
<thead>
    <tr>
        <th scope="col">First Name</th>
        <th scope="col">Last Name</th>
        <th scope="col">Position</th>
        <th width=120px scope="col">Location</th>
	<th width=60px scope="col">Phone</th>
        <th scope="col">Ext.</th>
	<th scope="col">Email</th>
    </tr>
</thead>

<tbody class="row-hover" role="alert" aria-live="polite" aria-relevant="all">';

while ($record = mysqli_fetch_array($mydata)) {

$count++;
if( $count % 2 == 0 ) {
$class = " class='odd'";
} else {
$class = "class='even'";
}

echo '<tr' . $class . '><td class="column-1">'; 
        echo $record['FName'];
        echo '</td><td class="column-2">'; 
        echo $record['LName'];
        echo '</td><td class="column-3">'; 
        echo $record['Position'];
        echo '</td><td width=120px class="column-4">'; 
        echo $record['Location'];
        echo '</td><td width=60px class="column-5">';
        echo $record['Phone'];
        echo '</td><td class="column-6">'; 
		echo $record['Ext'];
        echo '</td><td class="column-7">'; 
		echo $record['Email'];
        echo '</td></tr>';
	}
	
	echo '</tbody></table>';
	
	mysqli_statement_close($stmt);
	
	

mysqli_close($con);

My confusion is in the forming of the query in the mysqli_prepare statement and then how that relates to the parameters of mysqli_stmt_bind_param.   Then I want to use the results for the array that follows and gathered that "$mydata = get_result($stmt)" would accomplish this.

 

Am I on the right track.

[maxxd]

I'd highly recommend switching from mysqli to PDO if it's not too late for your project. It's much, much easier to deal with, especially regarding prepared statements. Just remember to turn off emulate prepared statements, which is on by default for reasons no one has yet been able to adequately explain...

[walleyeguy13]

I'd highly recommend switching from mysqli to PDO if it's not too late for your project. It's much, much easier to deal with, especially regarding prepared statements. Just remember to turn off emulate prepared statements, which is on by default for reasons no one has yet been able to adequately explain...

 

Thanks...   I'm working on the switch and have the following.   I get no errors, but I also do not get results.  I have read a few different iterations of how to deal with the variables in the prepare statement and the execute statement and am not sure if I have handled it properly.   I also went from the "while" statement to a foreach in order to render each record in a table row.  

error_reporting(E_ALL);
ini_set('display_errors', 1);

require 'dbconfig.php';

$con = new PDO("mysql:host=" .$db_host. ";dbname=staff" , $db_user, $db_password);
$con->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

echo '<form action="http://bemidji.wpengine.com/staff/staff-directory/staffdirectorysearch/" method="post">';
echo '<strong>Search For: </br></strong><input type="text" name="Search" placeholder="Last Name/First Name Search" /></br>';
// echo '<strong>Sort By: </strong><select name="Sort">
// <option value="Fname">First Name</option>
// <option value="Lname">Last Name</option>
// <option value="Position">Position</option>
// <option value="Location">Location</option>
// </select>';

echo '<input type="submit" value="Search" />';
echo '</form>';
echo '<a href="http://bemidji.wpengine.com/staff/staff-directory/">View All</a>';

if (isset($_POST['Search'])) {
$searchq = $_POST['Search'];
// $sortq = "Last Name";
// $sortq = $_POST['Sort']);
// echo '</br>Sorted By: ' . $sortq;


}

$stmt = $con->prepare("SELECT * FROM staffdata WHERE LName LIKE :srch OR FName LIKE :srch ORDER BY LName ASC");
$stmt->execute(array(":srch" => $searchq));

echo '<table id="staffdirectory" class="tablepress" >
<thead>
    <tr>
        <th scope="col">First Name</th>
        <th scope="col">Last Name</th>
        <th scope="col">Position</th>
        <th width=120px scope="col">Location</th>
	<th width=60px scope="col">Phone</th>
        <th scope="col">Ext.</th>
	<th scope="col">Email</th>
    </tr>
</thead>

<tbody class="row-hover" role="alert" aria-live="polite" aria-relevant="all">';

foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $record) {

$count++;
if( $count % 2 == 0 ) {
$class = " class='odd'";
} else {
$class = "class='even'";
}

echo '<tr' . $class . '><td class="column-1">'; 
        echo $record['FName'];
        echo '</td><td class="column-2">'; 
        echo $record['LName'];
        echo '</td><td class="column-3">'; 
        echo $record['Position'];
        echo '</td><td width=120px class="column-4">'; 
        echo $record['Location'];
        echo '</td><td width=60px class="column-5">';
        echo $record['Phone'];
        echo '</td><td class="column-6">'; 
		echo $record['Ext'];
        echo '</td><td class="column-7">'; 
		echo $record['Email'];
        echo '</td></tr>';
	}
	
	echo '</tbody></table>';

[Barand]

It looks like you are using LIKE without any wildcard characters. Try

$stmt->execute(array(":srch" => $searchq."%"));

EDIT Just noticed you use :srch twice - you need to treat those a two parameters Edited December 16, 2016 by Barand

[walleyeguy13]

It looks like you are using LIKE without any wildcard characters. Try

$stmt->execute(array(":srch" => $searchq."%"));

EDIT Just noticed you use :srch twice - you need to treat those a two parameters

 

 

Yes indeed.  After I posted, I added more PDO error reporting and discovered an HY093 error.   After a bit of searching I found that I cannot use :srch twice.  Thanks for the response!

[walleyeguy13]

For the sake of closure... my final working code (relevant parts anyway):

$con = new PDO("mysql:host=" .$db_host. ";dbname=staff" , $db_user, $db_password);
$con->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

$stmt = $con->prepare("SELECT * FROM staffdata WHERE LName LIKE :srch OR FName LIKE :srch_a ORDER BY LName ASC");
$stmt->execute(array(':srch' => "%".$searchq."%", ':srch_a' => "%".$searchq."%"));


echo ' -- html heading data for table';


foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $record) {

// html rendered for each record 

}

Thanks again for the pointers.