Certain words will not save to the database?

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
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Certain words will not save to the database?

Post 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?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Certain words will not save to the database?

Post by requinix »

What's your code and how exactly does it "break"?
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Re: Certain words will not save to the database?

Post 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;
    }
}
 
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Certain words will not save to the database?

Post 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'");
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Re: Certain words will not save to the database?

Post by icesolid »

OK. I tried the above and I still have the same result.

How do I go about using a "prepared statement"?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Certain words will not save to the database?

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Certain words will not save to the database?

Post 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);
(#10850)
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Re: Certain words will not save to the database?

Post by icesolid »

What would this do?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Certain words will not save to the database?

Post 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.
(#10850)
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Certain words will not save to the database?

Post 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.
Post Reply