jeudi 5 mai 2016

C++ insert a html string in postgresql database

I'm trying to insert an html string into a database column, but I'm having trouble with characters like quotes etc... which are inside the html. I'm using libpqxx as my C++ library to execute queries in the DB, and I'm using the function pqxx::quote() to add quotes between my string so that I don't have syntax troubles when I execute the insert query, but the problem is when I list the rows in the DB the html column shows only binary data. And I don't know why is that? here is the sql for creating the table:

  CREATE TABLE html_table(ID SERIAL PRIMARY KEY NOT NULL,URL TEXT NOT NULL,PARSERS TEXT NOT NULL,HTML TEXT);

And here is the C++ code:

void PostgreDb::insertRow(const std::string url,
                          const std::string parsers,
                          const std::string html)
{
    work W(*c);
    std::string sql = "INSERT INTO "+ table +"(URL,PARSERS,HTML)" \
            "VALUES (\'"+ url.c_str() +"\',\'"+ parsers.c_str() +"\',"+ W.quote(html) +");";

    W.exec(sql);
    W.commit();
}

Result in the DB after this piece of code is executed:

mydb=# select url,parsers,html from html_table;
              url               |     parsers      |      html       
--------------------------------+------------------+-----------------
 http://www.lupiga.com/         | name description | \x1F\u008B\x08
 http://ift.tt/hFWySe | name keywords    | \x1F\u008B\x08
(2 rows)

Note: I would say that quote() is not reliable here, anyone knows a better way to solve this?

Aucun commentaire:

Enregistrer un commentaire