Solved

mysql doesnt sanitise input unless you get the config right

Posted on 2014-11-23
16
107 Views
Last Modified: 2015-01-24
Hi

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
0
Comment
Question by:andieje
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 4
  • +1
16 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40460712
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)
0
 

Author Comment

by:andieje
ID: 40479394
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

thanks
0
 
LVL 58

Expert Comment

by:Gary
ID: 40479420
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.
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

Author Comment

by:andieje
ID: 40480542
Hmm. I wonder what config it was. I definitely read it and there was a log debate about it as you can imagine
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40490988
This page http://php.net/manual/en/function.mysql-real-escape-string.php and this page http://php.net/manual/en/mysqli.real-escape-string.php 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.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 40491702
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"?
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40492412
Actually, the 'mysql_real_escape_string' is part of the MySQL code, not just PHP.  It is used in other 'connectors' also.

http://dev.mysql.com/doc/refman/5.1/en/mysql-real-escape-string.html
0
 

Author Comment

by:andieje
ID: 40501552
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?

thanks
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40501562
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.
0
 

Author Comment

by:andieje
ID: 40501636
i also read mysql_escape string only works with utf collation

Perhaps it was this i read:
http://stackoverflow.com/questions/21091580/is-mysql-real-escape-string-vulnerable-to-invalid-utf-8-exploitation-eg-overlong

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


Thank yu - dot worry i'll have a millio more questions
0
 
LVL 58

Expert Comment

by:Gary
ID: 40501643
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
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40501666
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.
0
 

Author Comment

by:andieje
ID: 40525439
Found it:


http://dev.mysql.com/doc/apis-php/en/apis-php-pdo-mysql.html
PDO::MYSQL_ATTR_DIRECT_QUERY (integer)
Perform direct queries, don't use prepared statements.

From here: http://stackoverflow.com/questions/18803023/why-wouldnt-i-disable-pdomysql-attr-direct-query

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
0
 
LVL 58

Expert Comment

by:Gary
ID: 40525468
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
0
 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
ID: 40525491
I'm reading some contradictory statements - but nothing authorative about it.
And it's NYE so not investigating further.
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Creating and Managing Databases with phpMyAdmin in cPanel.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question