PHP+Mysql : update not work if use '&' contain WHERE

Question

This code not work :

UPDATE `test` SET `done`='1' WHERE `name`='x_&_y'

But this is work :

UPDATE `test` SET `done`='1' WHERE `name`='x_y'

Both of code return 1 value but first code not work and table not updated !

PHP code :

$value = 'x_&_y'; // send by $_GET['value']
$value = htmlspecialchars(trim($value),ENT_QUOTES,'UTF-8');
UPDATE `league` SET `leagueUpdateDone`='1' WHERE `leagueCountry`='$value';

something failed then; check for errors. You tagged as "php" also; *why?* There's no code to support the question and you even included "PHP" in the title. — Funk Forty Niner, Apr 04 '17 at 18:28
Can we see your table structure and content, if any sensitive data, hide it — Andrew Larsen, Apr 04 '17 at 18:28
Of course it doesn't work unless your data has 'x_&_y' in it. If you want to see if name has both X and Y, use proper SQL syntax. Spend a couple of minutes reading the docs... — Sloan Thrasher, Apr 04 '17 at 18:30
Then you should also know about **[How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1)** as well with binding specel char Hacker can use `');delete from table;` .. So then You can say goodbye to your data. allow all or Deny all. Do or Die — Abdulla Nilam, Apr 04 '17 at 18:30
people visit the "php" tag for a reason; they may think that they could submit an answer based on that. When there is no relevant code, you've basically wasted their time. If PHP isn't relevant, all instances of it need to be removed. We don't know if it's php-related, maybe it is and you may have done something wrong; who knows. — Funk Forty Niner, Apr 04 '17 at 18:31
@SloanThrasher, all query work correct. only update not work but return TRUE — My Name, Apr 04 '17 at 18:34
@Fred-ii-, this code work by SQL in PHPMYADMIN but not work with my php code — My Name, Apr 04 '17 at 18:35
If that is really your PHP code, the it won't work. Please post the real PHP code (remove server specific stuff like user ID, password, etc.) — Sloan Thrasher, Apr 04 '17 at 21:17

tadman · Accepted Answer · 2017-04-04T18:49:20.547

The problem is you're running your query wrong. Never, ever use htmlspecialchars on input if you're trying to match things. & is a reserved character in HTML, it will be mangled.

Your final query looks like:

'x_&amp;_y'

Instead use prepared statements with placeholder values, like this:

$stmt = $db->prepare('UPDATE `league` SET `leagueUpdateDone`='1' WHERE `leagueCountry`=?');

Then bind values against that. The procedure varies in implementation based on your use of mysqli or PDO.

Note: htmlspecialchars is only used for displaying HTML. Keep the content in your database as neutral as possible, never pre-escaped. You want to treat everything in your database as raw, escaping it for the context it's used in, be that JSON, HTML, email or otherwise, on a case-by-case basis. If you presume it's HTML that can make life very ugly if you need to undo that and re-do it for JSON, for example.

I don't know where you learned that htmlspecialchars technique, but it's highly probable this is cargo cult programming where incantations are used without their purpose being fully understood. This is a common problem with a lot of YouTube tutorial-type training where they drown you in code but offer very little in the way of theoretical foundation or practical explanations.

I'm trying not to be too hard on you here, you're just trying to learn, but it's important to understand the code you're using instead of just using it because someone told you to. Try to dig a little deeper, look up the documentation on the methods you're using. PHP has a fantastic manual with a comments section full of people helping to clarify any misunderstandings.

@MyName It's important to use, but it's important to use it at the right time and in the right place. — tadman, Apr 04 '17 at 18:49

PHP+Mysql : update not work if use '&' contain WHERE

1 Answers1