• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Escape in Mysql

Marcus

Well-known member
#1
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
#3
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
#5
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) . ')');
}
 

Marcus

Well-known member
#7
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
#10
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
#11
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
#12
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
#13
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.