mysql_real_escape_string() Question

[ubh]

Ok just reading up on this huge security hole in MySQL so I wanted to get somethings cleared up.

From what I have been reading this little guy "mysql_real_escape_string()" prevents SQL Injection Attacks but I dont understand how it prevents Injection attacks if it only for SELECT * FROM query..

Here is where I was reading up on it http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php.

Also should I have any concerns of something along the lines of this from a INSERT query??

Thanks.

[Fou-Lu]

mysql_real_escape_string is used to escape any data provided by a user for use in a query.
It is used for any query type; SELECT, UPDATE, REPLACE, DELETE, INSERT, ALTER, DROP.
The purpose is to prevent the user from entering data which prematurely terminates what the expected variable should be and extend the functionality of the query.
For example:

$input = $_GET['input'];
$obQry = mysql_query("SELECT * FROM User WHERE username = '" . $input . "'");

In normal usage this would create SELECT * FROM User WHERE username = 'Fou-Lu', but if I were to change my input to: ' OR 1'; the new query would become SELECT * FROM User WHERE username = '' OR 1'' resulting in at least one returned record (unless the table is empty). Mysql_real_escape_string would result in a query of: SELECT * FROM User WHERE username = '\' OR 1\'' which will return no result set (unless you have a username of ' OR 1').

Please note that in PHP mysql_real_escape_string command is not sensitive to the usage of magic_quotes_gpc directive, which could result in double escaping of data. I have a tutorial (listed in my sig) with how to remove GPC from a global level in you're scripts. This lets you freely use mysql_real_escape_string without worry of double escaping (which stacks everytime you update).

[ubh]

Cool thats exactly what I wanted to hear to gain a great grasp on this whole thing. I still how ever cant figure out what I am doing wrong to implement it into my site.

Here is my php code that use to post comments on my tutorials but for some reason when I try to incorporate the mysql_real_escape_string() to it I end up with empty entries in my database table.

<?php
$comment = $_POST['comment'];
$comment = mysql_real_escape_string($comment);

$name = $_POST['name'];
$name = mysql_real_escape_string($name);

$page = $_POST['page'];
include("../../scripts/php/db_cnt.php");

mysql_query("INSERT INTO comments (name, comment, page)
VALUES ('$name','$comment','$page') ");
?>

What am I doing wrong here?

[Fou-Lu]

If they are all empty, I'd suspect either the form is not using the post method or that the included file is overwriting the values for these variables.

Double check those two and post back. The INSERT query is correct. Oh yeah, and if you're not getting an insertion at all, check to see if you're connection exists. Also add an || die(mysql_error()); to the end of you're mysql_query call (in case its a problem with the structure)

[ubh]

Well I know it can't be the include file, this is simply my database connect file "db_cnt.php". If I remove the mysql_real_escape_string(); from my code and have it as I originally did then everything works fine.

Orignal Code:
<?php
$comment = $_POST['comment'];
$name = $_POST['name'];
$page = $_POST['page'];

include("../../scripts/php/db_cnt.php");

mysql_query("INSERT INTO comments (name, comment, page)
VALUES ('$name','$comment','$page') ");
?>

I then added the mysql_real_escape_string(); one at a time to $comments and $name. The first test resulted my $name and $page being inserted correctly but left my $comments out. Second resulted in only the $page name being inserted while my $name and $comments were not inserted.

I am using Ajax to preform the post and get for me so the wont have a page refresh to post and see their posted comments instantly.

Might Ajax have something to do with it?
Also I am using PHP5 might this be the problem?

[Fou-Lu]

Ajax and PHP5 are not the problem.
The problem is the connection. Move you're include above the calls to mysql_real_escape_string. Since the value is empty, its returning false (which means an error in mysql_real_escape_string). You need to have a connection established with the database prior to using it so it can be sensitive to the charset in use, as well as calling the real mysql_real_escape_string (built as a part of mysql).
I can't believe I missed that the first time through :P

[ubh]

HAHA wow I didnt see that one myself. Its working just fine now.
I guess for best practice I should always include my database connect prior to any mysql code I run from now on huh?

Also I found this out on the net and it might be a quicker way to using the mysql_real_escape_string(); but are there any security problems using it this way?

$comment = mysql_real_escape_string($_POST['comment']);
$name = mysql_real_escape_string($_POST['name']);

I just want to make sure I am doing it the right way lol.

Thanks.

[Fou-Lu]

Nope, that will work just fine. I'd always do the inclusion at the top unless you need it to be logic controlled (to determine what to include). Kinda like sessions, always start with session_start to prevent problems with header conflicts.

The only thing you may have a problem with is if magic_quotes are in effect. You'll need to strip them out first:

if (@get_magic_quotes_gpc()) // Mute it. PHP6 may not have this function
{
$_POST['name'] = stripslashes($_POST['name']);
...
}
$name = mysql_real_escape_string($_POST['name']);

[ubh]

Kool thanks a bunch bud!! Cleared up a lot for me!!