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

Escape in Mysql

Discussion in 'XenForo Development Discussions' started by Marcus, Oct 16, 2012.

  1. Marcus

    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
     
  2. Robbo

    Robbo Well-Known Member

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

    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
    :(
     
  4. Robbo

    Robbo Well-Known Member

    Well your database column needs to be changed then.
     
    Marcus likes this.
  5. Marcus

    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) . ')');
    }
     
  6. Robbo

    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 likes this.
  7. Marcus

    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.
     
  8. Robbo

    Robbo Well-Known Member

    Someone else here should know, I'm too tired to.
     
    Marcus likes this.
  9. Marcus

    Marcus Well-Known Member

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

    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();
     
    
     
    Chris D, Brandon Sheley and Robbo like this.
  11. Robbo

    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 likes this.
  12. Chris D

    Chris D XenForo Developer Staff Member

    Agree.

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

    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.
     
  14. Robbo

    Robbo Well-Known Member

    You wouldn't have had to edit the datawriter though...
     
    Marcus likes this.
  15. cclaerhout

    cclaerhout Well-Known Member

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

Share This Page