mysql - store embed codes (youtube, vimeo etc)

Question

How best to store the html for embedding?

the only ways I can think of are:

take the embed html and add <?php echo $var1; ?> where the id's go then store it in the db and use Eval to execute it.

or

insert a strange combination of characters to act as a marker to be replaced in php by the id's.

Answer 1

Option #2 is much safer - just in case someone manages to execute a SQL injection attack against your DB, they can't then exploit your embedding operation to execute injected PHP on the server side. The best they could hope for would be a phishing or XSS attack.

Another alternative is to format the appropriate data in XML and store in the database an XSLT to transform the data into the right embed code. That's probably overkill for your case, but more scalable and less error-prone than either of the above.

EDIT: Skeleton code for XML version

XML

<video>
  <url>http://example.com/video.flv&lt;/url&gt;
</video>

XSLT

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:transform version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;
  <xsl:output method="xml" />
  <xsl:template match="video">
    <xsl:element name="embed">
      <xsl:attribute name="src"><xsl:value-of select="url/text()" /></xsl:attribute>
      <xsl:attribute name="width">500</xsl:attribute>
    </xsl:element>
  </xsl:template>
</xsl:transform>

PHP

// assuming the XSLT above is stored in SomeTable.transform, and the above XML has been stored in $xml_text
$xml_doc = new DOMDocument();
$xml_doc->loadXML($xml_text);

$xsl_doc = new DOMDocument();
$xsl_doc->loadXML(GetXSLT("flv"));

$processor = new XSLTProcessor();
$processor->importStyleSheet($xsl_doc);
echo $processor->transformToXML($xml_doc);

function GetXSLT($type)
{
    $db = mysql_connect("user", "password", "host"); // not sure if I got the order right here, but whatever
    $res = mysql_query("SELECT transform FROM SomeTable WHERE type = '$type'"); // should use parameters instead of directly embedding the type here to avoid bugs and exploits, but whatever
    $array = mysql_fetch_assoc($res);
    return $array['transform'];
}

The nice part about this is that you can create a class to generate the input XML, and it can contain all the parameters you want to pass to your <embed> tag. If you don't add processing instructions to your XSLT to handle them, they'll be silently ignored. Make one class to generate the basic XML, and a subclass per media type you want to display, and generating the XML to pass the transforms should be easy.