PHP | issue with " & '

Question

I have just started up with few tutorials on PHP, the problem I often get with is " & '

following is the code where the issue is,

$query = 'INSERT INTO `users` (`email`, `password`) VALUES("'.mysqli_real_escape_string($link, $_POST["email"]).'", "'.md5(md5($_POST["email"]).$_POST["password"])'")';

This query simply takes inputs from the form fields, namely Email & Password & inserts into the database.

Can someone please help me where I am going wrong & any tips so that in future I dont commit any such mistakes?

Thanks...

Please take into account that we cannot read your mind or peek your monitor over your shoulder. Your code does not have any `&` character. What problem do you have? — Álvaro González, Jun 22 '16 at 11:24
BTW, you should find a tutorial that uses prepared statements. Having to use `mysqli_real_escape_string()` manually is so annoying. — Álvaro González, Jun 22 '16 at 11:24
Tutorials which learn you such ways of executing queries should be thrown off. — u_mulder, Jun 22 '16 at 11:25
@ÁlvaroGonzález he is using `&` to mean AND. His problem is with `"` and `'`, single and double quotes — bassxzero, Jun 22 '16 at 11:25
You should never use `md5()` for password hashing. If you're using php 5.5+, use `password_hash()` and `password_verify()`. — M. Eriksson, Jun 22 '16 at 11:28
@bassxzero How can you know that? Can *you* look at this screen? ;-) — Álvaro González, Jun 22 '16 at 11:30
Since the other answers are wrong. Your problem is that you're missing a `.` (concatenation) after `$_POST["password"])`. As for tips, use prepared queries. http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?lq=1 — bassxzero, Jun 22 '16 at 11:32
@bassxzero thanks for all ur help buddy, apparently the problem wasn't with the single & double quotes :) — Gaurav, Jun 22 '16 at 12:06
"bass notified u the problem" - Sorry, I'm slightly lost. Is he a co-worker or someone you know in real life? Otherwise he has no way to know that because the code you've shared does not have any attempt to use `&`, `AND` or any other boolean operator :-? — Álvaro González, Jun 23 '16 at 08:53

score 2 · Answer 1 · edited May 23 '17 at 10:32

2

You can use prepare and bind statement to escape such type of character and prevent from sql injection

$stmt = $link->prepare("INSERT INTO `users` (`email`, `password`) VALUES(?,?)");
$stmt->bind_param('ss', $_POST["email"], md5(md5($_POST["email"]) . $_POST["password"]));

/* execute prepared statement */
$stmt->execute();

Read Why not use MD5 for password hashing?

edited May 23 '17 at 10:32

Community

1
1

answered Jun 22 '16 at 11:25

Saty

22,443
7
33
51

score 1 · Answer 2 · answered Jun 22 '16 at 12:08

Answer

In MySQL, Strings (VARCHARs) are surrended with single quotes (ie '). You are using double quotes " arround your values. That's why you gave an error.

Explanation

If we take your $query and we replace calls to mysqli_real_escape_string and md5 with values foo and bar just to see what happens PHP replaces with the real values; the resulting string would be:

'INSERT INTO `users` (`email`, `password`) VALUES("'. 'foo' .'", "' . 'bar'. '")'

which gives after concatenations

'INSERT INTO `users` (`email`, `password`) VALUES("foo", "bar")'

You can clearly see that values are using " which will not work in MySQL.

Solution

The solution would be si,ply to use ' for values. This can be done in two ways:

Using " for the whole string and ' inside for values

"INSERT INTO `users` (`email`, `password`) VALUES('foo', 'bar')"

which gives

$query = "INSERT INTO `users` (`email`, `password`) VALUES('".mysqli_real_escape_string($link, $_POST["email"])."', '".md5(md5($_POST["email"]).$_POST["password"])"')";

Using ' for the whole string and escaping the ' of values using \

'INSERT INTO `users` (`email`, `password`) VALUES(\'foo\', \'bar\')'

which gives

$query = 'INSERT INTO `users` (`email`, `password`) VALUES(\''.mysqli_real_escape_string($link, $_POST["email"]).'\', \''.md5(md5($_POST["email"]).$_POST["password"])'\')';

Important Notes

Never inject PHP variables into an SQL query like this, use Prepared Statements instead
Never use md5 to hash passwords, use password_hash instead

Regarding `password_hash()`, if you're using PHP < 5.5 but > 5.3.7, you can use the [password-compat](https://github.com/ircmaxell/password_compat) library to get the same functionality. — M. Eriksson, Jun 22 '16 at 12:20
thanks @webNeat for taking out time for an effective explanation. i shall certainly use you advice, thanks again! — Gaurav, Jun 22 '16 at 17:00

score -1 · Answer 3 · answered Jun 22 '16 at 11:23

-1

this will work.

$query = 'INSERT INTO `users` (`email`, `password`) VALUES("'.mysqli_real_escape_string($link, $_POST[\"email\"]).'", "'.md5(md5($_POST[\"email\"]).$_POST[\"password\"])'")';

answered Jun 22 '16 at 11:23

Sandun Chathuranga

2,242
2
13
27

1

And in this case, it's actually wrong since the `$_POST` variable isn't inside any double quotes. – M. Eriksson Jun 22 '16 at 11:25
using forward slash ( \ ) u can escape the special characters. refere this link furthermore http://www.homeandlearn.co.uk/php/php7p7.html – Sandun Chathuranga Jun 22 '16 at 11:34

Atul Cws · Answer 4 · 2016-06-22T11:27:56.637

-1

mysqli_real_escape_string only make string storable in mysql preventing danger characters you also need to filter variables for xss also you can use htmlentities function to convert html special chars..for above purpose.

also instead nesting all var directly you can first load them into variables..

ex.

$email=$_POST['email'];


$email=mysqli_real_escape_string($email, $_POST["email"]);
$password='value';

now

$query = 'INSERT INTO `users` (`email`, `password`) VALUES("$email","$password")';

edited Jun 22 '16 at 11:27

answered Jun 22 '16 at 11:24

Atul Cws

7
3

This doesn't fix the ACTUAL problem – bassxzero Jun 22 '16 at 11:27

score -1 · Answer 5 · edited Jun 22 '16 at 13:08

-1

You didnt add dot(.) after password let it check. I hope this is useful for you. Either of these should work its just a matter of your preference.

Possible answer #1

$email = mysqli_real_escape_string($link, $_POST["email"]);
$pwd = md5(md5($_POST["email"]).$_POST["password"]);

$query = 'INSERT INTO `users` (`email`, `password`) VALUES("'.$email.'", "'.
$pwd.'")';

Possible answer #2

$query = 'INSERT INTO `users` (`email`, `password`) VALUES("'.mysqli_real_escape_string($link, $_POST["email"]).'", "'.md5(md5($_POST["email"]).$_POST["password"]).'")';

edited Jun 22 '16 at 13:08

bassxzero

4,838
22
34

answered Jun 22 '16 at 11:32

Vishnu Pratap

65
1
9

How about an explanation and some formatting. I hardly understand what you're trying to convey and I'm not new to the language like he is. – bassxzero Jun 22 '16 at 11:35
I changed your formatting a little more and added some detail. Go check it out – bassxzero Jun 22 '16 at 11:38

PHP | issue with " & '

5 Answers5

Answer

Explanation

Solution

Important Notes