Storing Code In MySQL Column

PHP
1

Hello,

I searched this forum and found this post:

I was going to add a post to it, but the forum software suggested I create a new thread since that thread was over 5 months old…

I’m working on a pet project and I’m looking for some hints/directions regarding best practices. I’m rolling my own blog that will display code snippets…similar to the code examples used in these posts and millions of others around the interweb. The code snippets are for display only and will never be retrieved and executed. I plan on using PDO and prepared statements for the database interactions.

Is it ok to store this type of code in a database?

Will PDO and prepared statements be sufficient for inserting into and selecting from columns that contain code snippets?

Any hints/tips/tricks to be aware of before I start?

Thanks,
Noob

2

Doh!

I didn’t think about the ‘what if I switch highlighters in the future’.

These forums rock!

Thanks for all of the feedback!
Noob

3

Nope, store the data, in the database. If you need to make it pretty, apply it when rendering.

What would happen further down the line if you decided to apply a different method of highlighting?

Yup, chaos.

If the highlighting process is intensive stuff, cache the highlighted data or, if you must, store it in another column.

Off Topic:

Touché Pawel. :stuck_out_tongue:

4

Always keep unprocessed text/code in the database and only manipulate before rendering. That way if you one day decide to change syntax highlighter you can do so whenever you want to. If you store it preprocessed by a syntax highlighter then you’re pretty much screwed.

5

Was just wondering, since I’m going to be passing the code through PEAR’s highlighter…would it be better to pass it through the highlighter and then enter it into the db or run it through the highlighter after retrieving it from the database?

Thanks,
Noob

6

Yup - definately make sure it’s all escaped before storing

7

Hello,

@Anthony, Thanks for the reply. Good point about it being just like plain old text if escaped properly. I never thought of it that way.

Thanks,
Noob

8

As long as it is properly escaped before display and will never be executed, you’ve nothing to worry about - it may as well be plain ol’ text.

9

I’ve not had any problems with PDO::prepare when inserting SQL code, however, some people have commented that for large statements _real_escape_string is more efficient.

Of course, assuming your on PHP5.

Cheerz,
Wil.

10

I’ll be entering the blog body content (including the code snippets) via an admin interface (form textarea).

I’m sort of new to PDO. Is it true/trustworthy that PDO does the escaping for you? The following code is a watered down version of what I’ll be doing, but sure feels weird not using some sort of _real_escape_string function on the $_POST variable. It feels like I’m missing something. Does this look proper?


$dsn = 'mysql:dbname=blog;host=localhost';
$user = 'dbuser';
$password = 'dbpass';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}


$stmt = $dbh->prepare("INSERT INTO blog_entries (blog_entry_body) VALUES (:blog_entry)");
$stmt->bindParam(':blog_entry', $_POST['blog_body']);
$stmt->execute();

TIA,
Noob