Escape in Mysql

Marcus

Well-known member
I get a escape error want to save / access a html document in a database.

Code:
$ch = curl_init("http://google.com");
$html = curl_exec($ch);
curl_close($ch); 
 
public function setHtml($html)
{
return $this->_getDb()->query(
  'REPLACE INTO xf_html (html)
  VALUES (' . mysql_real_escape_string($html) .'" )');
}
Code:
Mysqli statement execute error : Incorrect string value: '\xFCge de...' for column 'html' at row 1
 

Marcus

Well-known member
that returns the same error
Code:
Mysqli statement execute error : Incorrect string value: '\xFCge de...' for column  'html' at row 1
:(
 

Marcus

Well-known member
No, the quote function does not solve this problem. And I doubt it is because of the database table :(
Code:
CREATE TABLE `xenforo`.`html` (`html` VARCHAR( 10000 ) NOT NULL
) ENGINE = InnoDB;
Code:
 public function actionInsertWebsite()
{
  $ch = curl_init("http://google.com");
  $html = curl_exec($ch);
curl_close($ch);
 
$db = XenForo_Application::get('db');
  $q = $db->query('REPLACE INTO html (html) VALUES (' . $db->quote($html) . ')');
}
 

Robbo

Well-known member
Why are you doing fetchAll in the second one? It should be query.

It's probably your charset or something? :/
 

Marcus

Well-known member
It was a copy & paste error, of course I used query.

Actually, with http://www.google.com it works! But I want to fetch another website with <?xml version='1.0' encoding='ISO-8859-1'?> in the header, there I get the error message.
 

tenants

Well-known member
REPLACE INTO << Ouch, I never had to use this when using the XenForo Datawriter correctly
VARCHAR( 10000 ) << ouch, something tells me you should be using a large object column (try a text column)
mysql_real_escape_string($html) << Sounds dangerous, why not let the Datawriter handel anything that could be abused

I would becareful with inserting and then displaying html, particulary if it's from users input (even when using the datawriter, users could attempt to manipulate the HTML <b>bold test</b> <script>sendMeTheAdminsCookie();</script>

What I did when first learning how to insert/replace in XF is to look at how the core and how other plugins do it

Have a look at Jaxels datawriters (that's where I looked 1st), for instance, one of my DataWriters (slightly simplified):

Code:
class XenKingDir_DataWriter_Directory extends XenForo_DataWriter
{
 
protected function _getFields()
    {
        return array(
            'sf_xenkingdir_directory_node' => array(
                'node_id'            => array('type' => self::TYPE_UINT, 'autoIncrement' => true),
                'description'        => array('type' => self::TYPE_STRING, 'default' => ''),   
            )
        );
    }
 
    protected function _getNodeModel()
    {
        return $this->getModelFromCache('XenKingDir_Model_Directory');
    }
 
    protected function _getExistingData($data)
    {
        if (!$nodeId = $this->_getExistingPrimaryKey($data))
        {
            return false;
        }
        return array('sf_xenkingdir_directory_node' => $this->_getNodeModel()->getNodeById($nodeId));
    }
 
    protected function _getUpdateCondition($tableName)
    {
        return 'node_id = ' . $this->_db->quote($this->getExisting('node_id'));
    }
 
}
You'll need to write the model funtion "getNodeById($nodeId)" or whatever it is you use. Then, if you do come accross something where you need to a "replace into" _getExistingData will automatically be called when the primary key is found using setExistingData, for instance when doing this:

Code:
      $writer = XenForo_DataWriter::create('XenKingDir_DataWriter_Directory');
      $writer->setExistingData($node_id);    // in this case, the primary key exist so we can set as existing data
      $writer->bulkSet($arrayForTheRest);           
      $writer->save();
 

Robbo

Well-known member
Agree with the guy above btw. Just too tired to actually point out all the issues with what you are doing and expected someone to do it for me :p Listen to what he has said :)
 

Chris D

XenForo developer
Staff member
Agree with the guy above btw. Just too tired to actually point out all the issues with what you are doing and expected someone to do it for me :p Listen to what he has said :)
REPLACE INTO << Ouch, I never had to use this when using the XenForo Datawriter correctly
VARCHAR( 10000 ) << ouch, something tells me you should be using a large object column (try a text column)
mysql_real_escape_string($html) << Sounds dangerous, why not let the Datawriter handel anything that could be abused

I would be careful with inserting and then displaying html, particulary if it's from users input (even when using the datawriter, user could attempt to manipulate the HTML <b>bold test</b> <script>sendMeTheAdminsCookie();</script>

What I did when first learning how to insert/replace in XF is to looked at how the core and how other plugins do it

Have a look at Jaxels datawriters (that's where I looked 1st), for instance, one of my DataWriters (slightly simplified):

Code:
class XenKingDir_DataWriter_Directory extends XenForo_DataWriter
{
 
protected function _getFields()
    {
        return array(
            'sf_xenkingdir_directory_node' => array(
                'node_id'            => array('type' => self::TYPE_UINT, 'autoIncrement' => true),
                'description'        => array('type' => self::TYPE_STRING, 'default' => ''),         
            )
        );
    }
 
    protected function _getNodeModel()
    {
        return $this->getModelFromCache('XenKingDir_Model_Directory');
    }
 
    protected function _getExistingData($data)
    {
        if (!$nodeId = $this->_getExistingPrimaryKey($data))
        {
            return false;
        }
        return array('sf_xenkingdir_directory_node' => $this->_getNodeModel()->getNodeById($nodeId));
    }
 
    protected function _getUpdateCondition($tableName)
    {
        return 'node_id = ' . $this->_db->quote($this->getExisting('node_id'));
    }
 
}
You'll need to write the models "getNodeById($nodeId)" or whatever it is you use. Then, if you do come accross something where you need to a "replace into" _getExistingData will automatically be called when the primary key is found using setExistingData, for instance when doing this:

Code:
      $writer = XenForo_DataWriter::create('XenKingDir_DataWriter_Directory');
      $writer->setExistingData($node_id);    // in this case, the primary key exist so we can set as existing data
      $writer->bulkSet($arrayForTheRest);                 
      $writer->save();
Agree.

I think you've now had 3 developers advise you on the right way of doing things. Don't ignore us :(
 

Marcus

Well-known member
I promise you to try using the Datawriter next time :D

But - for this small testing it was much easier to use replace as I could easily change collations just in mysql, and not have to also edit my Datawriter.
 
Top