MySql query with SELECT / WHERE / OR stopped working

[kthiesen]

The following query used to work on older PHP. I have upgraded to PHP 4.3.2 and now it stopped working.

No problems connecting to db - can even print the variables used for search. But it seems as if the variables aren't presented to the query!?

Any ideas?

$result = mysql_query("SELECT username, name, surname, email FROM forums_auth WHERE username ='$_POST[pnr]' OR name LIKE '$_POST[fnavn]' OR surname LIKE '$_POST[enavn]' ORDER BY surname, name ASC") OR DIE ( mysql_error() );

[dreamcatcher]

Hi kthiesen,

Welcome to Webmaster World. :)

Have you tried:

$result = mysql_query("SELECT username, name, surname, email FROM forums_auth WHERE username ='$_POST[pnr]' OR name LIKE '%$_POST[fnavn]%' OR surname LIKE '%$_POST[enavn]%' ORDER BY surname, name ASC") OR DIE ( mysql_error() );

dc

[krakrazor]

I would highly recommend you reading over this article.

[us2.php.net ]

Here's how you should do it.

$pnr = mysql_escape_string($_POST[pnr]);
$fnavn = mysql_escape_string($_POST[fnavn]);
$enavn = mysql_escape_string($_POST[enavn]);

$result = mysql_query("SELECT username, name, surname, email FROM forums_auth WHERE username ='" . $pnr . "' OR name LIKE '" . $fnavn . "' OR surname LIKE '" . $enavn . "' ORDER BY surname, name ASC") OR DIE ( mysql_error() );

[kthiesen]

Hi,

I think I have tried both, but I will test it again.

Thanks
Kristen

[kthiesen]

I have tried krakrazors suggestion as I've recently read up on the dangers off SQL-injections (last night!)

It works when used with correct inputs are put into the input fields of the form, but if a search is done with only one of the three input-fields i get several hundred hits ;-/?

My old code had the use of wildcard injection:

LIKE %$var%

and that worked fine. Is that not possible in PHP 4.3.2?

[seanpecor]

First, if you do a heavy amount of SQL in code as I do, you might want to consider a format like:

####
function mes($s) { return mysql_real_escape_string($s); }

$result = mysql_query(sprintf("SELECT username, name, surname, email FROM forums_auth WHERE username ='%s' OR name LIKE '%s' OR surname LIKE '%s' ORDER BY surname, name ASC", mes($_POST[pnr]), mes($_POST[fnavn]), mes($_POST[enavn]))) OR DIE ( mysql_error() );
####

As far as including array referenced variables inside strings like "My name is $_POST[name]", you should switch to "My name is {$_POST[name]}". The curly brackets enclose the variable and tells PHP where the token begins and ends. I doubt this is your problem but it will prevent headaches down the road.

As for returning a shmazillion records with the suggested query, my guess is that you need a more sophisticated query builder. Or a kludge. I'll show you the kludge first :) Not my first choice but it demonstrates how to solve the problem I believe you're experiencing. What seems is happening is that LIKE '%$var%' is returning everything because mysql thinks LIKE '%%' matches everything. I'm going to go out on a limb here, I think the older version of PHP you were using might have been returning something in those $_POST references. Like if $_POST[enavn] was undefined then maybe it would return "Undefined" so when your query was built it would come back as (name LIKE '%Undefined%') so Mysql would NOT find a match on all records? I could be way off, but a psychologist once said I have a tendency to over-intellectualize things ;) Here is the kludge:

####
// Kludge the variables, if they're undefined then make their contents unique with some characters bound not to appear in fields.
$_POST[pnr] = isset($_POST[pnr])? $_POST[pnr]:"!@#?";
$_POST[fnavn] = isset($_POST[fnavn])? $_POST[fnavn]:"!@#?";
$_POST[enavn] = isset($_POST[enavn])? $_POST[enavn]:"!@#?";

function mes($s) { return mysql_real_escape_string($s); }

$result = mysql_query(sprintf("SELECT username, name, surname, email FROM forums_auth WHERE username ='%s' OR name LIKE '%s' OR surname LIKE '%s' ORDER BY surname, name ASC", mes($_POST[pnr]), mes($_POST[fnavn]), mes($_POST[enavn]))) OR DIE ( mysql_error() );
####

As for my own preferred method of building queries that may have any number of conditionals depending on what variables may be specified, I do a format like this:

####
function mes($s) { return mysql_real_escape_string($s); }

$q = sprintf("username='%s'", mes($_POST[pnr]));

if (!empty($_POST[fnavn])) $q .= sprintf(" or name like '%%%s%%'", mes($_POST[fnavn])):"";
if (!empty($_POST[enavn])) $q .= sprintf(" or surname like '%%%s%%'", mes($_POST[enavn])):"";

$result = mysql_query($q) or DIE (mysql_error());
####

Hope this helps a little.

Sean

[krakrazor]

Made a small mistake, need to put quotes around the post vars

$pnr = mysql_escape_string($_POST["pnr"]);
$fnavn = mysql_escape_string($_POST["fnavn"]);
$enavn = mysql_escape_string($_POST["enavn"]);

$result = mysql_query("SELECT username, name, surname, email FROM forums_auth WHERE username ='" . $pnr . "' OR name LIKE '" . $fnavn . "' OR surname LIKE '" . $enavn . "' ORDER BY surname, name ASC") OR DIE ( mysql_error() );