Escape in Mysql

[Marcus]

I get a escape error want to save / access a html document in a database.

$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) .'" )');
}

Mysqli statement execute error : Incorrect string value: '\xFCge de...' for column 'html' at row 1

 

[Robbo]

$this->_getDb()->quote() instead of that real escape function.

 

[Marcus]

that returns the same error

Mysqli statement execute error : Incorrect string value: '\xFCge de...' for column  'html' at row 1

 

[Robbo]

Well your database column needs to be changed then.

 

[Marcus]

No, the quote function does not solve this problem. And I doubt it is because of the database table

CREATE TABLE `xenforo`.`html` (`html` VARCHAR( 10000 ) NOT NULL
) ENGINE = InnoDB;

 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]

Why are you doing fetchAll in the second one? It should be query.

It's probably your charset or something? :/

 

[Marcus]

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.

 

[Robbo]

Someone else here should know, I'm too tired to.

 

[Marcus]

I solved it! I had to use utf8_encode($html);

 

[tenants]

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):

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:

      $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]

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 Listen to what he has said

 

[Chris D]

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 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):

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:

      $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]

I promise you to try using the Datawriter next time

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.

 

[Robbo]

You wouldn't have had to edit the datawriter though...

 

[cclaerhout]

You should read this tutorial. It's really a great one.