Apostrophe problem

[Square1]

Hi all:

For 99% of my queries I've been binding parameters, but on this particular query that won't work. So I am using mysqli real escape string.

I have a form that is entering a name ('Hacksaw' Jim Duggan) and am runnning the variable through the mysqli real escape string. I echo the page that is receiving the variable and as expected it is coming up as \'Hacksaw\' Jim Duggan. But the problem is, the DB is not reading \'Hacksaw\' Jim Duggan properly as no results are being returned.

if (isset($_REQUEST["player"])){ $aPlayer = mysqli_real_escape_string($link, $_REQUEST["player"]);}

What am I missing?

Thanks!

[bradgrafelman]

Square1;10990084 wrote:

I echo the page that is receiving the variable and as expected it is coming up as \'Hacksaw\' Jim Duggan.

Before or after sanitizing the data? If before, then you've got double-escaping problems going on. If after, then all appears to be well so far.

Square1;10990084 wrote:

But the problem is, the DB is not reading \'Hacksaw\' Jim Duggan properly as no results are being returned.

What does 'reading properly' even mean? No, you won't find \'Hacksaw\' anywhere in the database since that wouldn't make sense... unless his name actually included the backslashes (in which case they'd need to be escaped as well).

[johanafm]

Square1;10990084 wrote:

For 99% of my queries I've been binding parameters, but on this particular query that won't work.

Why?

[Square1]

johanafm;10990130 wrote:

Why?

Just the "kitchen sink" type mentality. My perception is nothing beats binding parameters as far as sql injection protection. I do understand that done correctly MySQL Real Escape is fine and that done incorrectly binding wpn't work, but when I started learning PHP the beginning of the year I just started with binding.

[Square1]

bradgrafelman;10990099 wrote:

Before or after sanitizing the data? If before, then you've got double-escaping problems going on. If after, then all appears to be well so far.

What does 'reading properly' even mean? No, you won't find \'Hacksaw\' anywhere in the database since that wouldn't make sense... unless his name actually included the backslashes (in which case they'd need to be escaped as well).

The raw, unescaped variable is appearing without hashes. The escaped variable is appearing with hashes. So this is as expected.

Where do I go from here?

[Weedpacket]

I think johanafm is asking "why" it won't work for that particular query (whatever it is).

[johanafm]

Correct. The reason I ask is that I see no reason it shouldn't work... so it seems natural to resolve that issue and use prepared statements across the board if that's what you do for all other queries.

[Square1]

Well, this problem is not limited to apostrophes. It has happened where the variable has a period and paranthases (sic) too. But not every variable with these characters causes it to break, but it only breaks when a non-letter is in the variable. Most records with apostrophes are working fine. I cannot figure out what is is working for some and not others.

Here is a simplified query. Note the FROM section. To get 'Hacksaw' Jim Duggan to appear I need to comment out as follows. Something is causing these INNER JOINS to break with some queries.:

SELECT player
			FROM
				Events
				#INNER JOIN Players ON Players.plID = Events.plID
				INNER JOIN Promoters ON Promoters.prID = Events.prID
				#INNER JOIN PlayerDetails ON PlayerDetails.plID = Events.plID
				#INNER JOIN Sports ON Sports.sportCode =  Players.sportCode
				INNER JOIN Locations ON Locations.loID = Events.loID

			WHERE player = '".$aPlayer."'";

So I don't believe it is a hash/apostrophe issue. I am checking language types on MySQL and my pages but they all appear to match. I manually copied over the prID (and other IDs) from table to table to make sure they all match but still no luck.

I echoed the query and the variable is coming in fine.

How else can I trouble shoot this?

[johanafm]

If you escape your data properly, then no character is a problem. It's just that some characters HAVE to be escaped, while others do not. But just because user supplied data passed fine without proper sanitizing once doesn't mean your system is good to go.

But either way, how does this last thing relate to what you said about it not working as a prepared statement with bound parameters? The above is not a prepared statement. The above contains no bound variables.

Once again: Why can't you use prepared statements? How does it not work for you?

[Square1]

I didn't bind the parameters because I have a few LIKE comparisons in the query. At the time I wrote the query I was totally green (I am now a shade of maybe forest green or jade at best 😃 ) and couldn't figure out how to handle the LIKES. I will try rewriting the page with binding parameters as soon as my other messes are cleaned up!

[Square1]

After a weekend without internet and way too many hours of late nights scratching my head I found the answer to the problem. It lies in:

INNER JOIN PlayerDetails ON PlayerDetails.plID = Events.plID

This PlayerDetails table supplements the Players table. So in the Players table you would find a record for Player: "Reggie Jackson" with a plID: "23456321454sdfw". But then in the PlayerDetails tables you may find 3 records such as plID: "23456321454sdfw" and Team: "NY Yankees", plID: "23456321454sdfw" and Team: "Baltimore Orioles" and plID: "23456321454sdfw" and Team: "Oakland Athletics".

So any player that was a Team player had at least 1 record in the PlayerDetails table.

BUT...if a player was not a Team player, such as wrestler 'Hacksaw' Jim Duggan they did not have a record the PlayerDetails table so that blew out the query!!!

So another can of worms now opens up. Do I have to now add a record (more like a placeholder) for all non-team players to the PlayerDetails tables or is there a way to work around this in MySQL. My better judgement is to do it the long way and add the records instead of doing any workarounds which may cause other issues.

[johanafm]

Square1;10990374 wrote:

I didn't bind the parameters

well, I got the impression you did...

Square1;10990084 wrote:

For 99% of my queries I've been binding parameters, but on this particular query that won't work. So I am using mysqli real escape string.

Square1;10990374 wrote:

because I have a few LIKE comparisons in the query.

Which doesn't matter at all.

$stmt = $db->prepare('SELECT stuff FROM sometable WHERE someint > :int AND sometext like :txt');

$txt = 'string match%';
$int = 2;

# Query can be executed...

# ... either using bound parameters
$stmt->bindParam(':txt', $t, PDO::PARAM_STR);
$stmt->bindParam(':int', $id, PDO::PARAM_INT);
$stmt->execute();


# ... or by passing parameters when calling execute
$params = array('id' => $id, 'ct' => $t);
$stmt->execute($params);

At the time I wrote the query I was totally green (I am now a shade of maybe forest green or jade at best 😃 ) and couldn't figure out how to handle the LIKES. I will try rewriting the page with binding parameters as soon as my other messes are cleaned up![/QUOTE]

Square1;10990375 wrote:

BUT...if a player was not a Team player, such as wrestler 'Hacksaw' Jim Duggan they did not have a record the PlayerDetails table so that blew out the query!!!

The difference between inner and outer JOINS. Do note that INNER JOIN is the same as LEFT INNER JOIN, and LEFT JOIN is the same as LEFT OUTER JOIN.

[Derokorian]

Instead of inner join you could use left join. This would leave in results from the players table without requiring corresponding entries in the playerdetails table. HTH