How to run php code and show code from mysql table ?

[chrisdee]

Hello. I've been moving my flat file based website over into mysql tables so I can do online adding, editing and deleting of content.

I have a lot of php scripts wich I used show_source to view, but I cant run php code from my mysql tables. I wonder if this is possible or not.

PHp is great for looping though content and presenting in tables, but if this is not possible with content from mysql tables I wonder if I should go back to my flat file website.

[Kudose]

Can you be more specific about what you think you can't do with MySQL? There are lots of mathematical and string functions built into MySQL.

http://dev.mysql.com/doc/refman/5.1/en/functions.html

[chrisdee]

Well as I said I have a website where I present different content.
Often I present things with tables, so I would like to run code so I can loop through all the rows and columns of a table istead of having to manually writing lots of < tr> and < td> for each cell.

Maby I should explain how my site is built up first. I have several mysql tables where I in each table post store id, title and content. It's in the content (wich is blob) I would like to write php code wich loops through tables, etc that is shown to the user visiting my site.

Below are my files :

connect.

<?php
# Variables
$username               = "username";
$password               = "password";
$host                   = "localhost";
$database               = "website";

#connects to the MySQL server
mysql_connect($host,$username,$password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
?>

navigation.php

<?php
include('connect.php');

$sql = "SHOW TABLES FROM $database";
$result = mysql_query($sql);

while ($tables = mysql_fetch_row($result)) {
 if(ereg('tree|stats|telefonliste|text',$tables[0])) {
}
elseif($tables[0]=='siteinfo') {
 echo "<a href=index.php?id=1&table=siteinfo>".ucfirst($tables[0])."</a><br>";
}
else {
 echo "<a href=index.php?table=".$tables[0].">".ucfirst($tables[0])."</a><br>";
 }
}
mysql_free_result($result);
?>

index.php

<?php
echo "<table border=1 height=500><tr><td class=m>";
include("navigation.php");
echo "</td>";

echo "<td class=n width=800>";
$order=$_GET['order'];
if(!$order) {
 $order="title";
}

$table  =$_GET['table'];
# Connecting to mysql table
$result = mysql_query("SELECT * FROM $table order by $order asc");
$loop = mysql_num_rows($result);

for ($i=0; $i<$loop; $i++) {
 $myrow = mysql_fetch_array($result);
 $title = $myrow["title"];
 $content = $myrow["content"];
 $id = $myrow["id"];
 print "<a href='index.php?id=$id&table=$table&title=$title'>$title</a> | ";
}
$id = $_GET['id'];

$sql = "SELECT * FROM $table WHERE id='$id'";
$query = mysql_query($sql) or die(mysql_error());
$result = mysql_fetch_array($query);

$title = $result["title"];
$content = $result["content"];

echo "</p><table width=100%>";
echo "<tr><td class=nav1>$title</td></tr>";
echo "<tr><td class=m>$content</td></tr>";
echo "</table>";
echo "<td></tr></table>";
?> 

[bpat1434]

This sounds like a good time to quote from someone much smarter than me.

If you're using eval, you're doing something wrong

You could store php code in a mysql column (and it doesn't have to be BLOB, it could be varchar or LONGTEXT). Then when you retrieve the said column, you could do something like this:

eval($row['phpcode']);

The problem here is security. What if inside that phpcode someone puts this:

highlight_file(__FILE__);exit;

Now guess what? They've got your database info & password out in the open. So now they can log in to your database, and wreak havoc. Not something you want to have happen.

The alternative is to keep php out of the mysql table, and use the table for what it was designed for: data. Keep data in the tables, let PHP handle the grunt of the logic (i.e. what's displayed where, etc.).

Now, if you wanted to get sums and totals and some basic conditional logic, you can do that in SQL with things like SUM() and COUNT() and CASE WHEN ... THEN ... ELSE ... END.

On another note...
In looking through you code, I see a giant security risk. Using user-inputted values directly into the SQL code is very dangerous. Currently if I went to your site and did sent the following in the query string:

?order=User&table=mysql.user

I could get a listing of all users in your mysql table. And with that a hash of their password, and what host they can connect from. So if you have a user which can connect from any host (i.e. their "Host" value is "&#37;") and a simple password, I can figure out what their password is by looking in a hash table or just brute-forcing it in my own time. Then come back and modify the database outside the site. And if you follow the same mantra as most control panels and name the database similar to that of the ftp user, they might have access to the filesystem itself.

Just some things to think about.

[chrisdee]

Wow, I did'nt know that. So what should I do to keep my site secure ?

[bpat1434]

Rethink your game-plan for the site and figure out how you can do what you want without having to allow user-input php code. You could offer "snippets" or "variables" which you will replace with pre-fabricated code and not user-input code.

[dagon]

some sort of template engine perhaps.