I cannot escape the strings!!

[thelung]

For some reason, no matter what I do, I cannot seem to escape this string that is causing error code when I try to insert record into databse.

Firstly, I must mention that the form which displays the problem variable echoes it's options from a database into a select form. Like this:

PHP Code:

                <select name='group'>
                    <option value='public'>Public</option>
            <?php   include '../globalFunctions.php';
                    con("netBase"); //connect to mysql and select database
                        $result= mysql_query("SELECT * FROM groups");
                        while($row = mysql_fetch_array($result)){
                          echo "<option value='";
                          echo mysql_real_escape_string($row['name'])."'>";
                          echo $row['name']."</option>";
                         }
                    
                    ?>
                </select>

I have the variable escaped when it is declared. During the course of trying to debug this, I've even went as far as stripping the tags as well as escaping the string.

PHP Code:

$group = mysql_real_escape_string(strip_tags($_POST['group']));
$address = strip_tags($_POST['address']);
$address2 = strip_tags($_POST['address2']);
$city = strip_tags($_POST['city']);
$state = strip_tags($_POST['state']);
$zip = strip_tags($_POST['zip']);
$date = date("Y-m-d"); 


I have a typical insertion query

PHP Code:

$query="INSERT INTO users (id, fname, lname, email, username, password, title, status, group, address, address2, city, state, zip, date)VALUES ('', '".$fname."', '".$lname."', '".$email."', '".$username."', '".$password."', '".$title."', '".$status."', '".$group."', '".$address."', '".$address2."', '".$city."', '".$state."', '".$zip."', '".$date."')"; 


But, I keep getting this error message instead of the "Inserted, way to go" message that is supposed to display after the fields have been successfully inserted into the database.

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group, address, address2, city, state, zip, date)VALUES ('', 'Harry', 'Harrison'' at line 1

I'm just assuming that this is a string issue. Considering that the "group" names which are echoed out into the form select boxes have apostrophes in them. Like "Andy's", or "Joseph's", etc.
I've even tried escaping the string before it is echoed into the form's select box, then in turn it being re-escaped as it is being posted from the form. Still no luck. Can anybody provide any insight, I'm T-totally stumped here.

[felgall]

mysql_real_escape_string is the old way of escaping data to be jumbled in with SQL in a query request (from before PDO and mysqli made it easy to keep the SQL and data separate using prepare/bind in place of query). There is no reason to use it in generating the HTML as you do in your first piece of code - you'd use html_specialchars() to escape code you want to output as part of the HTML.

With the other code you have there you are only escaping one field. Presumably one of the others can also contain characters that need to be escaped and that is what is causing the error. See if escaping the other fields as well makes a difference or alternatively use PDO to do away with the need to escape data being inserted into the database completely.

[abduraooft]

The error lies at

$query="INSERT INTO users (id, fname, lname, email, username, password, title, status, group,

GROUP is a reserved word of mysql, so you can't use them in the queries as such as the name of a table/field. You may enclose it using backticks (`), though it's better to avoid using them. See http://dev.mysql.com/doc/refman/5.5/...ved-words.html

[thelung]

Thanks abduraooft!
That was it! I can't believe all the modifications I've done to this code and it ends up being a reserved word. But, I've learned that one simple mistake can result in a whole lot of improvement in some code. Thanks guys!!