Page 1 of 1

Certain words will not save to the database?

Posted: Thu May 09, 2013 9:04 am
by icesolid
I am currently using $mysqli object to handle all of my database interactions. Locally when saving things to the database everything works. However, on the live server if the word "from" is in the body of text trying to be updated, it will break the query when trying to save. Previously when using the procedural mysql_ functions I had no issue as well.

If it helps, my local setup is Mac OSX and the live server is CentOS.

Any ideas?

Re: Certain words will not save to the database?

Posted: Thu May 09, 2013 12:29 pm
by requinix
What's your code and how exactly does it "break"?

Re: Certain words will not save to the database?

Posted: Thu May 09, 2013 12:32 pm
by icesolid
Breaks as in it just does not work.

Code looks something like this:

Code: Select all

DB::run("UPDATE `table_name` SET `field_name` = '$string' WHERE `id` = '$id'") 
DB is a class that simply does this:

Code: Select all

class DB {
    public static function run($sql)
    {
        global $mysqli;

        $result = $mysqli->query($sql) or die($mysqli->error);

        return $result;
    }
}
 

Re: Certain words will not save to the database?

Posted: Thu May 09, 2013 1:17 pm
by requinix
Did you make sure to use $mysqli->real_escape_string() on that $string before putting it into the query?

mysqli allows for prepared statements which takes care of stuff like that. If you don't use them (you aren't) then you have to escape everything yourself. Otherwise you are open to SQL injection attacks which let malicious users do all sorts of bad things to your database.

You could do it like

Code: Select all

class DB {
    public static function escape($string)
    {
        global $mysqli;
        return $mysqli->real_escape_string($string);
    }
}

Code: Select all

DB::run("UPDATE `table_name` SET `field_name` = '" . DB::escape($string) . "' WHERE `id` = '$id'");

Re: Certain words will not save to the database?

Posted: Thu May 09, 2013 1:58 pm
by icesolid
OK. I tried the above and I still have the same result.

How do I go about using a "prepared statement"?

Re: Certain words will not save to the database?

Posted: Thu May 09, 2013 2:32 pm
by requinix
You redo your DB class. At least parts of it. What's the full code and how does it get used?

But before that you need to explain what "breaks" and "does not work" mean. This SQL thing may be unrelated.

Re: Certain words will not save to the database?

Posted: Thu May 09, 2013 3:41 pm
by Christopher
You might want to try:

Code: Select all

$sql = "UPDATE `table_name` SET `field_name` = '" . DB::escape($string) . "' WHERE `id` = '$id'";
echo $sql;
DB::run($sql);

Re: Certain words will not save to the database?

Posted: Thu May 09, 2013 3:44 pm
by icesolid
What would this do?

Re: Certain words will not save to the database?

Posted: Thu May 09, 2013 4:16 pm
by Christopher
Show you the actual SQL that is being generated so you can possibly find the error. I suspect that there is some quoting problem since FROM is a SQL keyword.

Re: Certain words will not save to the database?

Posted: Thu May 09, 2013 5:00 pm
by requinix
It's also possible mod_security is installed and shutting down the request because it contains the word "from". Stupid, I know, but I've seen it do that.

Whether that could be the case or not depends on exactly how stuff is breaking.