Protecting against SQL injection (PHP/MySQL)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Josh1billion
Forum Contributor
Posts: 316
Joined: Tue Sep 11, 2007 3:25 pm

Protecting against SQL injection (PHP/MySQL)

Post by Josh1billion »

I was reading the Wikipedia article for SQL injection (link). The article recommends that you use the function "mysql_real_escape_string()" in situations like this (example pulled from article):

Code: Select all

$query_result = mysql_query
  (
        "select * from users where name = '"
    .
        mysql_real_escape_string($user_name, $dbh)
    .
        "'"
   );
My application makes many, many user data calls very similar to that (SELECT * FROM users WHERE username='$username') without using that protection. After skimming that article, it seems that I must replace all of my "WHERE username='$username'" queries with the safer method above. Like I said, I have many, many calls like that, so replacing them will take a while, so I want to make sure it's worth the effort before I start going through and changing it all.. so just looking for a second opinion here.

So my question, which I assume is a yes: is it worth the effort to go through and change all of this? Or is there a different/better alternative? Or (highly, highly doubtful) is it something I need not worry about?

Thanks in advance, guys. 8)

edit: also, should I do this with ALL queries which use a user-submitted value (including integers)? I'm assuming so.. boy, this is going to take a long time.. heh.
Last edited by Josh1billion on Sun Oct 07, 2007 10:16 pm, edited 1 time in total.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

The answer to this question will answer your question...

Is security important to you?



In all seriousness, any input should be escaped. No exceptions, ever. Your application is only as strong as the weakest link.
User avatar
Josh1billion
Forum Contributor
Posts: 316
Joined: Tue Sep 11, 2007 3:25 pm

Post by Josh1billion »

Of course, so I guess that's the yes I was expecting. :) I wasn't expecting a "no" unless there is a better alternative that someone would suggest, but if this is the best way to go, I will definitely get started right away.

Anything else I should know before I start?

edit: rather than using the function in the line of code which creates the query string, it would be fine if I just use it immediately as the user-submitted data is pulled, right? Example:

Code: Select all

$username = mysql_real_escape_string($_POST['username');
Post Reply