Solved

mysql doesnt sanitise input unless you get the config right

Posted on 2014-11-23
16
105 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 50

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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 …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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