Solved

mysql doesnt sanitise input unless you get the config right

Posted on 2014-11-23
16
101 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
  • 5
  • 5
  • 4
  • +1
16 Comments
 
LVL 58

Expert Comment

by:Gary
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:andieje
Comment Utility
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 82

Expert Comment

by:Dave Baldwin
Comment Utility
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 50

Expert Comment

by:Steve Bink
Comment Utility
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 82

Expert Comment

by:Dave Baldwin
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:andieje
Comment Utility
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 82

Expert Comment

by:Dave Baldwin
Comment Utility
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
Comment Utility
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
Comment Utility
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 82

Expert Comment

by:Dave Baldwin
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I'm reading some contradictory statements - but nothing authorative about it.
And it's NYE so not investigating further.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now