#1
  1. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,916
    Rep Power
    533

    Escaping HTML Strings Within MySQL


    I am totally copying an earlier post (link and answer below) and reposting it since it is almost 10 years old and perhaps there is a new solution....

    Hey,

    I am wondering if it is possible to escape HTML strings within MySQL.... I wish to prevent XSS, and would rather use MySQL directly that PHP's htmlspecialchars().

    Thanks for you time!



    http://forums.devshed.com/mysql-help...ql-182055.html

    Code:
    select replace(replace(replace(categoryname,'&','&'),'<','<'),'>','>') as newname
    from ...
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1045
    Hi,

    why would you do this on database level? Processing the data and preparing it for display is clearly the application's responsibility. I mean, you wouldn't use your SELECTs to add HTML tags, right?

    Database queries are for fetching and editing data, nothing else.

    In fact, I think this idea indicates that you don't properly separate the data model, data processing and the visual presentation (see the MVC pattern).
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,916
    Rep Power
    533
    Hi Jacques1,

    Yes, I do follow a MVC model. This particular use is just so dang convenient.

    Where do you typically escape the output before displaying on the screen? The controller tells model to get the data, and then passes the data to the view where it is displayed. Would you htmlspecialchars() in the model or the view? I was leaning toward the model as typically the code is less cluttered. But then I have such a nice $stmt->fetchAll(PDO::FETCH_ASSOC) in my model which is no longer so elegant, and instead need to iterate over each to escape. Which makes me question by decision to put escaping in the model, and maybe it should be put in the view....
  6. #4
  7. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    Escaping data is completely the responsibility of the view. In fact, your controller, model, and especially your database, should not even be aware of the fact that your application outputs HTML. None of them should be doing any escaping of output data in any way; this permanently binds your application to HTML as an output format and basically defeats the purpose of even having a view layer.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,916
    Rep Power
    533
    Thanks E-Oreo. After hearing you say it, I am in total agreement. Guess that is why a SQL version of htmlspecialchars doesn't and shouldn't exist.

IMN logo majestic logo threadwatch logo seochat tools logo