0

It seems ampersand symbol is causing problem for me some where in my flow of PHP, HTML, and MySql.

With the following database and table:

DROP DATABASE IF EXISTS testamp;
CREATE DATABASE testamp;
USE testamp;

DROP TABLE IF EXISTS acronym;
CREATE TABLE acronym(
    id               INT           NOT NULL  AUTO_INCREMENT  PRIMARY KEY,
    terminology      VARCHAR(255)  NOT NULL  UNIQUE,
    acronym          VARCHAR(255)  NOT NULL,
    index (terminology),
    index (acronym)
);

INSERT INTO acronym
(terminology, acronym)
values
 ( "United States", "US"  )
,( "Humanitarian and compassionate", "H&C"  )
;

It can be verified that "H&C" is in the database by select * from acronym where acronym like 'h%c';

Now with a .php, I give the user an input box for searching an acronym:

<input type="text" name="acronym" size="50" 
  placeholder="Enter an acronym" 
  autofocus
  onkeydown="if (event.keyCode == 13) { this.form.submit(); return false; }"
>

In case of POST, the user input is retrieved by calling a function, $acronym = test_input($_POST["acronym"]);, which sanitilizes the input

function test_input($data) {
  $data = trim($data);
  $data = stripslashes($data);
  $data = htmlspecialchars($data);
  return $data;
}

Then, the input is used to retrieve data from database:

$link = create_link() or die("create_link");
$stmt = $link->prepare(
    'SELECT acronym, terminology FROM acronym WHERE acronym like ? ORDER BY acronym'
);
$like_value="%$acronym%";
$stmt->bind_param("s", $like_value) or die("bind_param");
$stmt->execute() or die("execute");
$stmt->store_result();
echo "<p>num_rows: " .$stmt->num_rows .PHP_EOL;

Now, if what's inside the input box is "H&C" without qutes, no record is retrieved, Even though "US" gives me one record as expected.

sofname
  • 429
  • 1
  • 5
  • 20
  • 4
    Don't use `stripslashes()` or `htmlspecialchars()`. The former isn't required and the latter should only be used to **display** values in an HTML document – Phil Sep 28 '22 at 04:13
  • Thanks, @Phil. That fixed my problem! I was only copying a piece of code from w3school without thinking too much about it... Would you please create a simple answer? I would like to accept it as the answer, so it may benifit somebody else. – sofname Sep 28 '22 at 04:17
  • Out of curiosity, what made you think to use `htmlspecialchars()` in the first place? – Phil Sep 28 '22 at 04:18
  • It was a copy from w3school... – sofname Sep 28 '22 at 04:21
  • 2
    A truly terrible resource. Best to stick to the main PHP documentation or sites like https://phpdelusions.net/. In particular see https://phpdelusions.net/sql_injection#badpractices, there's a special section just for `htmlspecialchars()` – Phil Sep 28 '22 at 04:22
  • **Stop using `test_input()`.** This function will damage your data. – Dharman Sep 28 '22 at 07:25
  • Thanks, @Dharman. It's been removed already after Phil suggested that. – sofname Sep 28 '22 at 14:25

0 Answers0