Solved

mysql doesnt sanitise input unless you get the config right

Posted on 2014-11-23
16
103 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

919 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

20 Experts available now in Live!

Get 1:1 Help Now