I have an original PHP script for which I have lost the MySQL database dumb file and all table structure and data. Is there any way to rebuild/reconstruct the database having only the original PHP script files, so in effect reverse engineering the software?
Hi filledupwee welcome to the forum
No backup?
The script must have come with some
CREATE TABLE ____ IF NOT EXISTS
queries to initialize things after the database was created.
Use your text editor / IDE to search / find the string âcreate tableâ in the file(s).
1 Like
Yeah unfortunately not, the backup was somehow corrupt and since lost. I think it was due to a transfer I did from one laptop to another via a flash drive which was faulty.
The full code is still in place but there is no .sql file or such. I used GrepWin to search the entire code.
I am not sure about it but there is one folder âincâ which contains a db.php and db.inc.php - however neither have such a line âcreate table ____ if not existsâ.
I have browsed through the code and find table names and such but not knowing SQL very well or PHP in fact, I am not able to determine what data types need to be created to rebuild the DB myself.
Iâve tried to upload the file db.php as it seems to be for querying the database, however I guess Iâm too new here yet, so I have copied a snippet of the file here below, perhaps you might point me in the right direction?
<?
function db_connect()
{
global $db;
if ($db)
return;
$db = mysql_connect("localhost", MYSQLUSER, MYSQLPASS);
mysql_select_db(MYSQLDB, $db);
if (!$db)
{
echo "Couldn't open database!\n";
exit;
}
}
function db_disconnect()
{
global $db;
if ($db)
{
mysql_close($db);
$db = NULL;
}
}
function db_query($q)
{
global $db;
$result = mysql_query($q, $db);
return $result;
}
function db_fetch_array($result)
{
global $db;
$r = mysql_fetch_array($result);
return $r;
}
function db_query_row($q)
{
$result = db_query($q);
if (!$result)
return NULL;
$row = mysql_fetch_array($result);
return $row;
}
function db_query_value($q)
{
$row = db_query_row($q);
return $row[0];
}
function db_array_update($table, $a, $where)
{
$q = "update $table set ";
$b = NULL;
foreach($a as $key => $value)
{
if (is_int($key))
continue;
// if (isset($value) && $value != "")
$b[] = "$key='$value'";
}
$q .= implode(",", $b);
$q .= " where ".$where;
db_query($q);
}
function db_array_insert($table, $a)
{
$q = "insert into $table set ";
$b = NULL;
foreach($a as $key => $value)
{
if (is_int($key))
continue;
if ($value && $value != "")
$b[] = "$key='$value'";
}
$q .= implode(",", $b);
db_query($q);
}
function db_insert_id()
{
return mysql_insert_id();
}
function gen_id($table, $column, $size, $characters_allow)
{
for(;;)
{
$id = random_string($size, TRUE, $characters_allow);
$q = "select count(*) from $table where $column='$id'";
$count = db_query_value($q);
if ($count == 0)
break;
}
return $id;
}
function db_num_rows($result)
{
return mysql_num_rows($result);
}
?>
Youâll probably also want to consider writing a new PHP script as youâre using the old mysql_* extension which was removed in version 7 of PHP. You should now be using either the mysqli_* extension or PDO (for either of the two that you go with always use prepared statements if the data was supplied by the user)
1 Like
Thanks SpacePhoenix I will surely look into that. I was hoping it may be possible to rebuild the original run it and update it soon after - but I guess I may as well just go ahead and spend the money to have it rewritten in current standards. Too bad though because I had already spent quite some dollars on the coding.
Yes, once you get done with figuring out the table schemas that should be next on your todo list.
The script is a bit unusual in that most Iâve looked at pass the connection to the query rather than passing the query to where the connection is. But meh.
Anyway, without any CREATE TABLE queries youâll need to find other queries and best guess column definitions.
If the input is validated, that too should be of some help with determining what the column definitions might be.
I think if you look for the string âdb_queryâ you should be able to see the queries passed to the functions.
2 Likes
It looks like that is the only file which has âdb_queryâ which occurs 8 times. It is for a unique publisher/advertiser banner ad network which I have been meaning to launch since some time obviously.
Given the fact the code is deprecated and the db is lostâŚ
I think I will just go ahead and hire for the job to rebuild and improve the script based on whatever can be derived from the original.
Thanks Mittineague for your input.
1 Like
For example :
mysqldump --user=⌠--password=⌠--host=⌠DB_NAME > /path/to/output/file.sql
Which means your PHP code would look like this :
exec(âmysqldump --user=⌠--password=⌠--host=⌠DB_NAME > /path/to/output/file.sqlâ);
Of course, up to you to use the right connection information, replacing the ⌠with those.
1 Like
Iâm not sure that will be of use unless the tables exist in MySQL already. If the original poster lost those too, then using mysqldump wonât achieve anything.
1 Like
The snippet doesnât provide enough information about your scheme. Do you have another file which was used to inject the query from?
I actually one time had this same situation. I forgot to create backups and I only saved the MySQL data files only to find out that the data arenât fully backed up. So I ended up with broken files. I had to start over from scratch. So what I did was look at my INSERT, DELETE, and UPDATE queries and was able to restore everything that way.
1 Like
If you havenât already have a look through all of your folders on the off chance that there is a .sql file that has been stored. Sometimes people write a backup routine to write an sql dump to a file on the server.
Otherwise iâd probably install all of the php files on a server and make sure errors are turned on and then one by one go through the pages. The mysql error should show and be something like âPagesâ table not found etc and then once you have created the âpagesâ table itâs likely to say âIdâ column not found. Itâll take time but you should get enough information to rebuild the tables. Although there might be some autogenerated columns like timestamp on create date etc that might not show but you might be wise to add them.
hth
1 Like
Those are good ideas in fact. Installing the php files and going through the errors, that and searching for the insert, delete and update queries. Thanks a bunch.
There is no backup of the db and the .sql file is non-existent. So it seems this would be the most effective way to discover the table scheme.
Was the script written specifically for you when it was created or was it an âoff-the-shelfâ script?
Yes it was a custom script I bought. It is a simple yet unique ad network script for publishers and advertisers to place banners on websites or blogs and such with geo targeted ad placement.
Here is a short snippet of the approval.php file which has some more detail. This is just a small section of that file and is not complete. But it may offer some indication as to how it functions.
<?
ob_start();
require('main.php');
$theid = $_GET['id'];
$theaction = $_GET['action'];
$theidee = mysql_escape_string($theid);
$theact = mysql_escape_string($theaction);
session_start();
if (!isset($_SESSION['uid'])) {
$_SESSION['uid'] = $_REQUEST['uid'];
$_SESSION['pwd'] = $_REQUEST['pwd'];
}
$uid = $_SESSION['uid'];
$pwd = $_SESSION['pwd'];
db_connect();
$res = mysql_query("SELECT * FROM users WHERE uid='$uid' AND pwd='$pwd' and status='active'");
$re = mysql_fetch_array($res);
if(mysql_num_rows($res) != 0) {
// continue
} else {
unset($_SESSION['uid']);
unset($_SESSION['pwd']);
header("Location: http://www.mydomain.com/index.php?login=incorrect#two");
die(" ");
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<?
$hount = mysql_num_rows(mysql_query("SELECT id FROM campaigns WHERE campid='$theidee' AND ispaid='1' and publisher='$uid' and isactive='0' LIMIT 1"));
if ( $hount == "0" ) {
header("Location: http://www.mydomain.com/member.php");
die("$nbsp;");
} else {
$titties = mysql_query("SELECT * FROM campaigns WHERE campid='$theidee' AND ispaid='1' and approval='1' and isactive='0' and publisher='$uid' LIMIT 1")
or die(mysql_error());
while($titzy = mysql_fetch_array( $titties )) {
$tizban = $titzy['thebanner'];
$tizpay = $titzy['paypal'];
$tizurl = $titzy['theurl'];
$tizee = $titzy['publisher'];
$pricepaid = $titzy['pricepaid'];
$azone = $titzy['zoneid'];
$acamp = $titzy['campid'];
}
if ( $theact == "deny" ) {
mysql_query("INSERT INTO denials (payer, banner, siteurl, thepub, thezone, thecamp) VALUES('$tizpay', '$tizban', '$tizurl', '$tizee', '$azone', '$acamp' ) ")
or die(mysql_error());
mysql_query("DELETE FROM campaigns WHERE campid='$theidee' AND ispaid='1' and approval='1' and isactive='0' and publisher='$uid' LIMIT 1")
or die(mysql_error());
$whatwasdone = "denied";
$to = $tizpay;
$subject = '[MyDomain] Your Ad Campaign has been Denied!';
$message = 'Your ad campaign on '.$thethethe.' has been denied!
Youâll surely notice the coder had some unorthodox methods and a perverse sense of humor.
The code is a bit hard to read. Could you please edit your post to format the code: place three back ticks (`) on the line before the beginning of the code, and three backticks on the line after the end of the code. Thanks.
1 Like
Yes, you should be able to get at least close to what the database architecture and table schemas should be by finding those.
Depending on how many files there are I might be tempted to write a recursive script that uses regex to find the queries.
Else itâs just a matter of (tediously) looking at all the files and making a list of the tables, their columns, and anything else you can discern by looking at the code.
1 Like
Thanks for the tip - I will be sure to follow that protocol hereafter when posting code.
Yeah I will do just that and do my best to rebuild the db structure before I have it all redone and updated to the current PHP version.
As for an easy regex search tool you should check out grepWin - itâs great!
1 Like
Does the name of the folder the scripts are located in or any comments in the scripts give any clue as to either the name of the person who wrote the scripts or the name of the package?
I know who coded the script. However he is not available. This is not a package or any kind of generic script which can be bought online elsewhere. It was custom coded one of a kind.