PHP mysqli_real_escape_string problems

Question

I have several post variables that I run through the following:

$input_name =  mysqli_real_escape_string($dbc, trim($_POST['input_name']));

I have run several tests where I echo $input_name and other like variables before the insert query executes. The echo indicates that they are indeed getting escaped as they should.

However, when I login to phpmyadmin to look at my entries in the DB, I see that characters that should be escaped are not. Do I have a problem here? Is something happening between my variable declaration and the query that I am not aware of?

Are there php or server settings that could be influencing this?

note: I realize PDO is the way to go, I am just not there at this particular moment.

The function will change things like "hel'lo" into "hel\'lo", making it safe to insert into the database, once it's in the database it will look like "hel'lo" again, but will have been inserted and not cause an SQL error. — Dale
– Dale, Commented Dec 7, 2012 at 6:36
You escaped something and get it concatenated into your INSERT query string, the things inserted will not look like escaped. Think of INSERT INTO blah(field) VALUES ("This is \"stupid\""), it will insert This is "stupid" but not This is \"stupid\". — Alvin Wong
– Alvin Wong, Commented Dec 7, 2012 at 6:36
I suggest that if you're fighting with the hoops that mysqli is making you jump through, and it's so bad you have to post to SO to solve it, that it is an ideal time to start looking at PDO. — Andy Lester
– Andy Lester, Commented Dec 7, 2012 at 6:45
@AndyLester I don't see how the question is in any way mysqli-related. the data would look the same with PDO, too. — eis
– eis, Commented Dec 7, 2012 at 6:56

Abubakkar · Accepted Answer · 2012-12-07 06:40:36Z

1

The echo indicates that they are indeed getting escaped as they should.

This indicates that your characters are escaped.

when I login to phpmyadmin to look at my entries in the DB, I see that characters that should be escaped are not

Now as you are escaping means that you want to those characters as it is rather than PHP or you database taking them internally as delimiters.

Like if you want ' in your input as it is, so your are escaping it. So now when database(mysql) sees it that is is escaped so it won't considered it as a single quote that is used for string literals in MySQL.

If you don't escape it then MySQL will consider all the part between two ' as string literals.

So everything is fine, don't worry about it.

answered Dec 7, 2012 at 6:40

Abubakkar

15.7k8 gold badges58 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dillen Meijboom · Accepted Answer · 2012-12-07 06:37:40Z

1

The *_real_escape_string functions in PHP are only there to prevent SQL injection therefor it will only change " to \" and ' to \' so that the following query:

SELECT * FROM users WHERE pass = '' OR '1'='1 --

Will become:

SELECT * FROM users WHERE pass = '\' OR \'1\'=\'1 --

So that the injected value won't work.

answered Dec 7, 2012 at 6:37

Dillen Meijboom

9647 silver badges13 bronze badges

Collectives™ on Stack Overflow

PHP mysqli_real_escape_string problems

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related