PDO Connection Password can't have $ # ! characters

Question

I use ! @ # $ in my MySQL user's password

but when using these characters in my password, PDO can't connect to MySQL server

$servername = "localhost";
$username = "test";
$password = "!@#$test";
$dbname = "test";

try {
  $test = $_POST['test'];
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, 
  $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // prepare sql and bind parameters
  $stmt = $conn->prepare("INSERT INTO test (test) 
  VALUES (:test)");
  $stmt->bindParam(':test', $test);


  $stmt->execute();

It gives this error:

SQLSTATE[HY000] [1045] Access denied for user 'test'@'localhost' (using password: YES)

when removing !@#$ from password it works correctly

Turn on error reporting and you'll see the problem.`error_reporting(E_ALL);` & `ini_set("display_errors",1);` — WheatBeak, Jul 20 '17 at 20:36
it's because of the double quotes, it has nothing to do with UTF-8 — Funk Forty Niner, Jul 20 '17 at 20:39
Same as why `echo "!@#$test";` displays only `!@#` and generates a notice. — AbraCadaver, Jul 20 '17 at 20:45

Funk Forty Niner · Accepted Answer · 2017-07-20T20:48:05.213

10

This has nothing to do with UTF-8.

PHP thinks you have a variable name with a $ assigned to it.

The $password = "!@#$test"; with the double quotes is technically looking for a variable called $test and is trying to parse it.

It needs to be in single quotes:

$password = '!@#$test';

Note: The ! and @ also have special meaning in PHP.

! is not.
@ is an error suppressor and a character used in email.
# is a comment character.

Theoretically, error reporting should have thrown you a notice about an undefined variable.

http://php.net/manual/en/function.error-reporting.php

UTF-8 deals with queries and characters that won't display correctly when querying, depending on the db's/table's collation.

If and when that you start seeing ? in your query's output, then you would need to set the connection's DSN to UTF-8.

This is covered in the following Q&A:

UTF-8 all the way through

edited Jul 20 '17 at 20:48

answered Jul 20 '17 at 20:43

Funk Forty Niner

74,450
15
68
141

1

But `!@#` don't have any special meaning in a double-quoted string. – AbraCadaver Jul 20 '17 at 20:46
@AbraCadaver true, I just thought I'd note it "after" ;-) – Funk Forty Niner Jul 20 '17 at 20:46
1

for future reference OP, `error_reporting(E_ALL);` & `ini_set("display_errors",1);` will save a lot of headaches. – WheatBeak Jul 20 '17 at 20:48
@WheatBeak yes thanks. Matter of fact, I was adding the error reporting to answer. – Funk Forty Niner Jul 20 '17 at 20:48
@Fred-ii- it might be worth adding to your answering explaining how OP can include `$` in their password. EDIT, oh I just saw you did, was that there before? :P – WheatBeak Jul 20 '17 at 20:53
@WheatBeak the password assignment in single quotes should cover it as shown in the answer, or have I missed something? Edit: saw your edit too *lol* – Funk Forty Niner Jul 20 '17 at 20:54
@WheatBeak hehe no worries. – Funk Forty Niner Jul 20 '17 at 20:54
1

Of course if you really wanted to get technical there's `$password = "!@#\$test";` ;) – WheatBeak Jul 20 '17 at 20:55
2

Just personal preference really, I hate mixing `"` and `'` so for me it would be either change everything to `'` or just escape the `$` – WheatBeak Jul 20 '17 at 20:56

PDO Connection Password can't have $ # ! characters

1 Answers1