mysql doesnt sanitise input unless you get the config right


Im sorry for the vague question but i read an article that said that even if if php you use prepared statements of real_escape_string in mysql(i) calls then mysql doesnt actually sanitise the data unless there is a specific config setting of the database. The article did an explain or something on the query and showed how it hadnt been sanitised correctly

I cant find the article or my bookmark now. I thought you experts night know. It may even be outdated now but as morpheus says, its a splinter in my mind now

PS On reflection i cant be 100% sure whether they did say it applied to pdo
They also said real escape string is only reliable with utf8 too (but i use pdo)

Many thanks experts
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If you are using PDO then use prepared statements and bound values - that way there is no need to sanitize the data as the sql is seperate from the data.

The only sanitization that you may have to do is if this data may be used on a page and you should prevent page injection (ie change HTML tags to their entity equivalent to prevent js injection)
andiejeAuthor Commented:
I know you are very experienced but the page was explicit that you had to set a config  setting in  mysql, even with pdo, otherwise mysql does not sanitise the data .Perhaps it was an oldish article and things have changed but it went through examples and showed you the results and they were not sanitised even though you had used p

I dont think the article was that old though and i've never found it since. But Internet articles aren't  infallible. Perhaps ti was this

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

the top line creates 'real' prepared statements apparently, I'm not sure if that means other other prepared statement are imaginary :)

any comments from mysql dbas appreciated too

Thats only to do with caching prepared statements.
If you are using prepared statements with bound values there is zero possibility of sql injection or the need to sanitize the data as the sql statement and the data never come into contact with each other ergo no sanitization needed.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

andiejeAuthor Commented:
Hmm. I wonder what config it was. I definitely read it and there was a log debate about it as you can imagine
Dave BaldwinFixer of ProblemsCommented:
This page and this page tell you exactly what the 'real_escape_string' functions do.  The only question is what do you mean by 'sanitise the data'?  Note that the purpose of those functions is to make it so the string doesn't 'break' the query.  They do not prevent bad data but since they 'escape' quotes, it makes it difficult to 'inject' unwanted data that might 'break' a query.
Steve BinkCommented:
More importantly, the escape functions in PHP are just that... in PHP.  Those functions are all about what you are sending to MySQL.

MySQL simply reads what you send it, and tries to execute it.  If it is valid SQL, it works; if not, it doesn't.  A configuration setting in MySQL has no way to impact what PHP does or how it does it.

That said, Dave's question is very relevant - what do you mean by "sanitise the data"?
Dave BaldwinFixer of ProblemsCommented:
Actually, the 'mysql_real_escape_string' is part of the MySQL code, not just PHP.  It is used in other 'connectors' also.
andiejeAuthor Commented:
ok, i really wish i could remember, Perhaps it was bad writitng like this not. I dont think purusing it will create progress.

But it was read_eascape_string that did require the config setting.

I use pdo and someone on an mysqli project asked for help and I rememeber this page as it ellicited the response'you're kidding'

If was for mysqli is there a config settting relating to the collation?

Dave BaldwinFixer of ProblemsCommented:
config settting relating to the collation
There is a default collation setting in MySQL.  However every database and even every table can be set to a different collation.
andiejeAuthor Commented:
i also read mysql_escape string only works with utf collation

Perhaps it was this i read:

Perhaps ive mudded over time, i will request permission to delete the question

Thank yu - dot worry i'll have a millio more questions
You keep talking about MySQL string escaping but you are using PDO, PDO has it's own methods as described above.
There is no reason for deleting the question as it has been answered
Dave BaldwinFixer of ProblemsCommented:
i also read mysql_escape string only works with utf collation
That's not true.  Gary's right, there is no reason to delete this question.  Just go ask another one.
andiejeAuthor Commented:
Found it:
Perform direct queries, don't use prepared statements.

From here:

I stumbled upon the (imho rather poorly documented) fact that by default PHP PDO has the flag MYSQL_ATTR_DIRECT_QUERY enabled for its MySQL driver.

This means rather than actually use prepared statements, it emulates the behaviour of prepared statements. This means it replaces the placeholders client-side with escaped values and just sends the full query to the database as-is.

So you have to set this flag to false to get true prepared statements
From the first paragraph of the PHP linked page.

PDO_MYSQL will take advantage of native prepared statement support present in MySQL 4.1 and higher. If you're using an older version of the mysql client libraries, PDO will emulate them for you.

There is no reason in the world you should be even using v4.x at this stage
I'm reading some contradictory statements - but nothing authorative about it.
And it's NYE so not investigating further.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.