htmlspecialchars equivalent for mysql

Question

I am querying a database for userInput text which was obtained from a form, and will display that text in an HTML page. To prevent XSS, I need to iterate over the results and apply htmlspecialchars(), Sure, I change change fetchAll() to fetch() and apply htmlspecialchars() there, but it sure would be nice to use a native MySQL function and include it in my query. I searched but couldn't find one. Does one exist? Am I able to create my own? Thanks

$sql ='SELECT userInput FROM table WHERE fk_id=123';
$stmt = db::db()->query($sql);
return $stmt->fetchAll(PDO::FETCH_ASSOC);

Huh? How would this prevent `XSS`? Do you mean sql injection? — Madbreaks, Dec 01 '12 at 16:40
There is something like `mysql_escape_string()` and `mysql_real_escape_string()` but they just escape the problematic characters. I don't know what are their equivalences in PDO. — MahanGM, Dec 01 '12 at 16:41
@Madbreaks. No, I am not saving but retrieving. What if the user included a little JS in their form data. It is recommended to save data in its original state, and escape before using. — user1032531, Dec 01 '12 at 16:45
Never save data in its original state without ensuring that it is not a SQL injection by, eg, applying `mysql_real_escape_string()` — thesimm, Dec 01 '12 at 16:46
Why storing in original state, not already escaped by `htmlspecialchars`? — dev-null-dweller, Dec 01 '12 at 16:46
@thesimm Op is using PDO, used properly there should be no reason to use the *deprecated* `mysql_*` functions. — Madbreaks, Dec 01 '12 at 16:47
@MahanGM. mysql_escape_string() and mysql_real_escape_string() were designed to escape the query, and not escape special characters in HTML. — user1032531, Dec 01 '12 at 16:48
I agree with @dev-null-dweller, it would be better to escape the content prior to storing it if possible, otherwise you'll always have to account for the fact that you could be storing malicious code in your DB - not a good idea. Some future developer may not know that and use the raw content. — Madbreaks, Dec 01 '12 at 16:49
@user1032531, "but they just escape the problematic characters.", mentioned above. BTW, agreed to dev-null-dweller. — MahanGM, Dec 01 '12 at 16:50
@dev-null-dweller. Why it should be stored in its original state: 1) You lose the original data; if something goes wrong will the escaping, you'll have nothing but a big mess. 2) Different escaping methods and options can easily make the data inconsistent. 3) You bind the data to a specific purpose (HTML); if you need to use it in a different context, you have to revert the HTML escaping first. 4)In your HTML, you completely rely on the data being escaped already, even though there's absolutely no guarantee for this (what if somebody has edited an entry or manually added it?) — user1032531, Dec 01 '12 at 16:50
Or if you don't believe me, Googled "htmlspecialchars before or after storing in database", and first (4) results are: http://stackoverflow.com/questions/4598802/should-we-html-encode-special-characters-before-storing-them-in-the-database. http://stackoverflow.com/questions/1970880/store-html-entities-in-database-or-convert-when-retrieved. http://stackoverflow.com/questions/7245440/should-htmlspecialchars-be-used-on-information-on-input-or-just-before-output. http://stackoverflow.com/questions/4882307/when-to-use-htmlspecialchars-function — user1032531, Dec 01 '12 at 16:58
It's not that I don't believe, I'm just asking if you had any good reason to store it in original form. If this data is displayed in many places and inserted/edited only one/two it makes more sense to unescape it before editing in one place that to escape in many. And believe me, this is much better idea than writing your own function for escaping in database. — dev-null-dweller, Dec 01 '12 at 17:08
Always store the data in its original state in the database. **always** — PeeHaa, Dec 01 '12 at 17:59
@PeeHaa I'm not registering with **password** on any website you created ;) — dev-null-dweller, Dec 01 '12 at 18:34

echo_Me · Answer 1 · 2012-12-01T18:15:31.430

1

It all depands on what data you are saving. If you are saving data to directly display on your website, then obviously you'd need htmlspecialchars() and the likes of it. PDO obsolete the use of mysql_real_escape() and such by using prepared statements.

As an iron law, every string must be escaped for its proper destination: HTML strings undergo HTML escaping, MySQL strings undergo MySQL escaping etc.

EDIT :

When you get it out of the database ( or display ANY of it to users as html) then you escape it again ready for that that place it is going next (html) with htmlentities() etc to protect your users from XSS attacks.

edited Dec 01 '12 at 18:15

answered Dec 01 '12 at 17:52

echo_Me

37,078
5
58
78

1

Agree Peter. I am not talking about MySQL escaping, but only HTML escaping. Since it is user provided data and will be displayed on a page, it needs something like htmlspecialchars(). My original question was whether this could be done directly in MySQL? – user1032531 Dec 01 '12 at 18:03
take a look here [link](http://php.net/manual/en/function.mysql-real-escape-string.php) – echo_Me Dec 01 '12 at 18:11

score 1 · Answer 2 · answered Jun 02 '19 at 08:54

1

UPDATE messages SET message = REPLACE(REPLACE(REPLACE(message,'&','&amp;'),'<', '&lt;'), '>', '&gt;')

answered Jun 02 '19 at 08:54

Sergey Shuchkin

2,037
19
9

Although this code might solve the problem, a good answer should also explain what the problem is and how the code helps. – BDL Jun 02 '19 at 11:02

score 0 · Answer 3 · answered Aug 16 '18 at 10:49

6 years later and I think the answer to the first part is still "no, there isn't"... but to the second, "yes": you can create your own, if you wish, using MySQL stored procedures.

Anyway, probably the best way to handle the situation of a cross-site scripting attack is to remove any script tags or inline script before the html is stored into the database, whether as a MySQL stored procedure (tricky!) or in the script before you send the html to the database.

user1032531 · Accepted Answer · 2012-12-02T14:18:49.477

-2

Escaping the data shouldn't be done in the database, but in the PHP application. Furthermore, it should always be done in the view and not the model.

edited Dec 02 '12 at 14:18

answered Dec 01 '12 at 19:52

user1032531

24,767
68
217
387

1

What a louzy answer. No one said MVC is used. And the question is not being answered. This could have better been a comment. And even one I would disagree with. – nl-x Jun 02 '14 at 07:35

score -3 · Answer 5 · answered Dec 01 '12 at 17:31

My suggestion: use objects. Create your own class with setters/getters and define how it will store/return your data.

ex:

class UserRow{
    public $id;
    public $email;
    protected $bio;

    public function getBio(){
        return htmlspecialchars($this->bio);
    }

    public function __set($property, $value){
        $this->$property = $value;
    }

    public function __get($property){
        $getter = 'get'.$property;
        if(is_callable(array($this,$getter))){
            return $this->$getter();
        }else{
            return $this->$property;
        }
    }
}

And

$stmt = $pdo->query("SELECT * FROM user");
$stmt->fetchAll(PDO::FETCH_CLASS, 'UserRow');

htmlspecialchars equivalent for mysql

5 Answers5