On 07/10/2011 05:46, Govinda wrote:
Hi everyone
I have read many many articles and blog posts in the last few days to bolster my (still mostly newbie) understanding of the factors that play in to preventing various methods of SQL injection prevention.. and by now I am well aware that most everyone (expert) here says prepared statements are the most secure method of (string hacking) SQL-injection prevention.. even to the point of saying that one common (and at least previously-popular) alternative "mysql-real-escape-string" is "..silly and technically insecure..".
I am learning and using the CodeIgniter (CI) framework for my current project and, not wanting to leave myself vulnerable, I read posts on the CI forum on this topic, to find out if I could (or needed) to use prepared statements in CI.. and I read one forum thread where one dev shows how to hack the core system of CI so that it can use PDO (for prepared statements) instead of the built-in ActiveRecord (or "Query Bindings") which apparently rely on mysql-real-escape-string. In that thread, the debate goes back and forth, as it does in other threads.. and while the sentiment that prepared statements are better because they remove the need to keep being ahead of the char-escaping chase... I never did see any example of *how* mysql-real-escape-string fails. The only thing I ever read that does show mysql-real-escape-string possibly failing is in the example in this article:
http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string
or rather an article referred to there, here:
http://ilia.ws/archives/103-mysql_real_escape_string-versus-Prepared-Statements.html
Hi,
Please bare in mind that the above example uses the mysql object, not
mysqli (the improved mysql extenstion). The above attack doesn't work
in mysqli (at least not in my setup!). There is also the issue of which
character set is used by mysqli_real_escape_string. To be sure issue
the mysqli_set_charset() call after connecting to the db.
With regards to procedure in MySQL, there is one gotcha that caught me
out. There is no way to ALTER a procedure, you must DROP and reCREATE
it again. This is fine in most GUIs such as Heidi SQL, as all the work
is done for you, but you must realize that when you issue the DROP
command it also DROPS any permissions you have on that procedure.
So to alter a procedure you must DROP, CREATE and then re-create the
permissions. If you have a replicated setup with multiple servers and
multiple web sites accessing them, this can be allot of permissions to
re-apply.
In web programming (or any other sort) you must ALWAYS assume that any
input is malicious. Using this principle you can then build secure
applications.
For example, the above article takes the $_POST input without any
validation. In this case it is a username. On my sites I tell users
when registering that their username can only contain certain
characters. If any other characters are supplied I strip them out.
I can then pass data to mysqli_real_escape_string knowing that there
will be no multi-byte characters in there to trip it up.
The same goes for other input, validate it all. There are built in
functions to do this for you [1], or you can build your own.
It is not just input to watch out for, you must also Escape your output
to mitigate Cross Site Scripting (XSS)[2] and other attacks.
There are many automated tools out there to help you test your
applications. You wont know its breakable unless you try to break it!
And if you don't, others will.
I suggest you go to the OWASP site [3] (The Open Web Application
Security Project) and have a look around.
Regards
Ian
--
[1] http://php.net/manual/en/ref.filter.php
[2] http://en.wikipedia.org/wiki/Cross-site_scripting
[3] https://www.owasp.org/index.php/Main_Page
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
