htmlspecialchars() in MySQL

marque

Does anyone know of a MySQL fuction that acts similar to htmlspecialchars()?

I know i can just use mysqlspecialchars() on the field once I have the result but it could be really handy to do it in the SQL. Especially when using CONCAT, etc...

sneakyimp

the point of using some kind of function on a data before you send it to mysql is that there are certain characters that will 'break' your sql statement so that it is either a) no longer valid or b) downright dangerous.

if your data is a string and has single quotes or double quotes in it, your SQL can be broken.

if you don't run something like mysql_escape_string() addslashes() before you send it to mysql, then you run a security risk.

marque

addslashes() is added to every insert and update.

What I need is something that converts "&" to "& amp;"... etc. Just like htmlspecialchars()

sneakyimp

i'm not 100% certain, but i don't think there is any native mySQL function to do that. there is a way to expand mySQL with your own functions, but that sounds like more work to me than just doing htmlspecialchars() on your data when you construct your sql statement:

$sql = "INSERT INTO myTable SET foo='" . htmlspecialchars($data) . "'";

if htmlspecialchars isn't doing everything you need, consider htmlentities()

marque

Yes,

That stated is obvious, but what I need is on the select statement not any insert or update.

The table containes non escaped html entities.

I need to select with the special charactors converted to a standards compliant form before it returns to php

an example would be

select CONCAT('<pre>',HTMLcode,'</pre>') AS HTMLCode
FROM mytable

Now I could just do...

while($row = mysql_fetch_assoc($result)) {
  echo "<pre>".htmlspecialchars($row['HTMLcode'])."</pre>\n";
}

but the result I am trying to reach is a little more complicated than my examples.

sneakyimp

ok...i sort of get it. data is in the database, you want to apply the html-ization step as you are pulling it out.

same rule applies as far as i know. mySQL has no native routine to convert any string to an HTML code.

not sure what conditions you could possibly have in your sql/queries that would make it easier to apply the html codes before you get the info out of html. the only condition i can think of is where you are trying to do a JOIN in your sql or something where one field is raw and the other is the html code equivalent.

sorry, but without additional info, i'm not going to be any help at all.

marque

I thought as much,

The documentation didn't give me anything I could use.

It's simply a convienence thing.

The use would be to get the rows of data all compacted into one row with the SQL using If and Case statements in the SQL rather than in php.

The result would render one field per row that I can use to render my information through a generic rendering class.

I am big on standards compliance so simply putting CONCAT('<td>',foo,'</td>') just wont cut it. cause foo may have invalid chars.

mtmosier

I don't believe there's anything built in to MySQL for this. As sneakyimp mentioned you'll probably have to do a user defined function. This project provides source for a set of user defined functions which you might be able to use as a base.