Your File Skeleton

Below is the template for the file requested.

<?php /* 1. Copy and paste this into a text program (i.e. Notepad, Wordpad, etc.) 2. In the Configuration Area below, replace all the 'CHANGE's with the data for your database 3 Save the file with a .php extensions like 'querytool.php' 4. Put it on your webserver 5. Browse to the url it is at using a web browser 6. You will see a page that details the connection status to let you know of any issues. 7. If no issues connecting, you can use this to run queries on the database */ // ++++++++++++++++++ Above: Instructions // ++++++++++++++++++ Below: Configuration Area $CONFIG['ServerName']="CHANGE"; // server name of database $CONFIG['UserName']="CHANGE"; // user name to connect to database $CONFIG['Password']="CHANGE"; // password to connect to database $CONFIG['Database']="CHANGE"; // name of database to connect to // ++++++++++++++++++ Above: Configuration area // +++++++++++++++++ Below: Data functions function html_Results($in_Results) // spits out html for results of query {$ret="\n\t"; $int_Size=count($in_Results); if ($int_Size>0) // query has results will show {$ret.="<table align=\"center\" cellspacing=\"0\" cellpadding=\"0\" border=\"0\">\n\t<tr>"; foreach ($in_Results[0] as $tmp_Field) $ret.="\n\t\t<td class=\"form_HeaderCell\">".$tmp_Field."</td>"; $ret.="\n\t</tr>"; // shows field headers in its own styled row for ($counter_i=1; $counter_i<$int_Size; ++$counter_i) // loops through all result rows and displays them in table {$ret.="\n\t\t<tr>"; foreach ($in_Results[$counter_i] as $tmp_Field) $ret.="\n\t\t\t<td class=\"form_HCell".($counter_i%2)."\">".$tmp_Field."</td>"; $ret.="</tr>"; } $ret.="</tr></table>"; } else $ret.="<center><h2 style=\"background: #FF0000\">No Results</h2></center>"; return $ret; } function show_Area($in_Area, $in_Status) // shows/hides various areas of page based on page's status {$ret="<div"; if (($in_Area=="Tool") && ($in_Status==0)) $ret.=" style=\"visibility: hidden;\""; // if connection error will hide Tool area if (($in_Area=="Results") || ($in_Area=="Status")) // results and status areas get indented if shown and hidden if not right status {$ret.=" style=\"padding-left: 10px;"; if ((($in_Area=="Results") && ($in_Status!=1)) || (($in_Area=="Status") && ($in_Status!=3))) $ret.=" display: none"; $ret.="\""; } return $ret.">"; } function DB() // makes database connection once and returns that connection for use {global $CONFIG; static $ret; if ($ret===NULL) $ret=mysqli_connect($CONFIG['ServerName'], $CONFIG['UserName'], $CONFIG['Password'], $CONFIG['Database']); return $ret; } function html_QueryStatus($in_Error) // shows status of query that was run from the page {$ret="\n\n\t\t<p style=\"padding-top: 24px;\"><center><h2 style=\"background: #"; if ($in_Error=="Valid") // query is valid {$ret.="00CC33\">Query Successful</h2></center>\n\n\t\t<p class=\"main_Explain\">"; $ret.="The submitted query was run without issue and its results can be viewed below."; } else {$ret.="FF0000\">Query Error</h2></center>\n\n\t\t<p class=\"main_Explain\">"; $ret.="This tool will only run SELECT queries and the query submitted "; if ($in_Error=="SELECTFROM") // No select or from clause {$ret.="did not contain a '<b>SELECT</b>' or '<b>FROM</b>' keyword so it wasn't "; $ret.="sent to the server."; } elseif (substr($in_Error, 0, 7)=="Invalid") // invalid character {$ret.="failed a test that flags possible SQL injections. Queries run with this "; $ret.="tool cannot contain the string '<b>".substr($in_Error, 8)."</b>'."; } else // error from server will display it {$ret.="was returned in error by the server. Below is the specific error "; $ret.="message:<p align=\"center\"><b>".$in_Error."</b>"; } } return $ret; } function html_ConnectionStatus($in_ConnectionError, $in_QueryError) // shows connection status in upper left area {global $CONFIG; $bool_Valid=True; if (strlen($in_ConnectionError)>1) $bool_Valid=False; // determines if error present or not $ret="\n\n\t\t<center><h2 style=\"background: #"; if ($bool_Valid) $ret.="00CC33\">Connection Successful"; else $ret.="FF0000\">Connection Error"; $ret.="</h2></center>\n\n\t\t<p class=\"main_Explain\">"; // displays color coded alert about connection if ($bool_Valid) // connection valid, will display that message {$ret.="The user name, password and database ('<b>".$CONFIG['Database']."</b>') information submitted was "; $ret.="accepted by the MySQL server ('<b>".$CONFIG['ServerName']."</b>') and a connection was made. "; $ret.="The Query Tool section to the right can now be used to run queries against the database."; } else // issue with connection will explain {$ret.="There are 3 connection steps to this tool--contacting the server, accepting the credentials "; $ret.="and accessing the specific database. This connection "; if (strpos($in_ConnectionError, "getaddrinfo failed")!==false) // failed at server info {$ret.="failed immediately because it was not able to even find '<b>"; $ret.=$CONFIG['ServerName']."</b>'. Make sure 'ServerName' is typed correctly in the Configuration "; $ret.="Area of this file."; } else if (strpos($in_ConnectionError, "using password:")!==false) // failed at credentials {$ret.="passed the first step and connected to the server ('<b>".$CONFIG['ServerName']; $ret.="</b>'), but the server did not accept the credentials. Make sure 'UserName' and 'Password' are "; $ret.="typed correctly in the Configuration Area of this file. Also, make sure those credentials "; $ret.="are set up and have the proper authorization on the server itself."; } else if (strpos($in_ConnectionError, "to database")!==false) // failed at database {$ret.="passed the first step and connected to the server ('<b>".$CONFIG['ServerName']; $ret.="</b>', passed the second step and the credentials were accepted but failed to connect to "; $ret.="the '<b>".$CONFIG['Database']."</b>' database. Make sure 'Database' is typed correctly "; $ret.="in the Configuration Area of this file. Also, make sure those credentials "; $ret.="are set up and have the proper authorization to that specific database itself."; } else {$ret.="failed at an unknown step. Below is the raw error message:"; $ret.="\n\t\t\t<center><b>".$in_ConnectionError."</b></center>"; } } $ret.="<p align=\"center\"><a href=\"".$_SERVER['SCRIPT_URI']."?in_Connection=All#status\">View All Connection Data</a>"; if (($bool_Valid) && ($in_QueryError!="NoQuery")) $ret.=html_QueryStatus($in_QueryError); // if connection valid will show query status area return $ret; } // ++++++++++++++++++ Above: Data functions // ++++++++++++++++++ Below: Variables $SQL['SQL']=""; // sql statement to be executed $SQL['Query']=array(); // result back from mysql server of query $SQL['Result']=array(); // array of results $SQL['MaxResults']=50; // maximum number of results query will show, will add LIMIT to any SQL $SQL['Rows']=-1; // number of rows returned from query $SQL['Tables']=array(); // holds all tables in database $SQL['Error_Connect']=""; // error message about connection to database $SQL['Error_Query']="NoQuery"; // error message about specific query user submitted $SQL['Status_Connect']=1; // status of connection, 1=valid, 0=invalid $SQL['Status_Query']=0; // status of query, 0=no query, 1=valid, 2=invalid, 3=show conn data $SQL['Status']=1; // status of page //0=invalid connection; 1=valid connection no query; 2=valid connection & query; 3=valid connection & invalid query; 4=show all conn data $counter_i=0; // counters to loop through data $str_Invalid=""; // holds which invalid word SQL contains and caused it to fail $arr_Invalids=array("DELETE", "INSERT", "ALTER", "TRUNCATE", "DROP", "UPDATE", ";"); // invalid input terms for user submitted query, page only runs SELECT queries // ++++++++++++++++++ Above: Variables // ++++++++++++++++++ Below: Data processing DB(); $SQL['Error_Connect']=mysqli_connect_error(); if (strlen($SQL['Error_Connect'])>1) $SQL['Status_Connect']=0; // initializes database connection, gets any errors and updates status if ($SQL['Status_Connect']==1) // successful connection will pull all tables from database for use in dropdown {$tmp_query=mysqli_query(DB(), "SHOW TABLES FROM ".$CONFIG['Database'].";"); while ($r = mysqli_fetch_array($tmp_query)) $SQL['Tables'][]=$r[0]; } if (isset($_GET['in_Connection'])) // user wants all connection data {$SQL['Status_Query']=3; $SQL['Result']=array(array("Function", "Value"), array("get_host_info", mysqli_get_host_info(DB())), array("get_server_version", mysqli_get_server_version(DB())), array("get_server_info", mysqli_get_server_info(DB())), array("connect_errno", mysqli_connect_errno()), array("connect_error", mysqli_connect_error()), array("get_client_version", mysqli_get_client_version()), array("get_client_info", mysqli_get_client_info(DB())), array("get_proto_info", mysqli_get_proto_info(DB())), array("sqlstate", mysqli_sqlstate(DB())), array("stat", mysqli_stat(DB()))); $counter_maxi=count($SQL['Result']); for ($counter_i=1; $counter_i<$counter_maxi; ++$counter_i) // will change first element of each function into link to w3school for it {$tmp_Function="mysqli_".$SQL['Result'][$counter_i][0]; $SQL['Result'][$counter_i][0]="<a href=\"https://www.w3schools.com/php/func_".$tmp_Function; $SQL['Result'][$counter_i][0].=".asp\" class=\"form_Link\" target=\"w3c\">".$tmp_Function."()</a>"; } } if ($SQL['Status_Connect']==1) // successful connection will run queries on it {if (isset($_GET['in_Table'])) // user submitted table to get structure of {$SQL['Status_Query']=1; $SQL['SQL']="SHOW FULL COLUMNS FROM ".mysqli_real_escape_string(DB(), $_GET['in_Table'])." FROM ".$CONFIG['Database']; } if (isset($_POST['in_SQL'])) // user submitted SQL {$SQL['Status_Query']=1; $SQL['SQL']=trim($_POST['in_SQL']); if (substr($SQL['SQL'], -1)==";") $SQL['SQL']=substr($SQL['SQL'], 0, -1); // if last character a semicolon, removes it if ((strtoupper(substr($SQL['SQL'], 0, 6)) !== "SELECT") || (strpos(strtoupper($SQL['SQL']), 'FROM') === false)) // makes sure submitted query starts with SELECT and contains FROM {$SQL['Error_Query']="SELECTFROM"; $SQL['Status_Query']=2; } if ($SQL['Status_Query']==1) // valid SQL so far, will make sure its limited to $SQL['MaxResults'] results at most {$tmp_int=strpos($SQL['SQL'], " LIMIT "); if ($tmp_int) // SQL already contains LIMIT keyword, will make it less than $SQL['MaxResults'] if over $SQL['MaxResults'] {$str_tmp=substr($SQL['SQL'], $tmp_int + 7); $tmp_int=strpos($str_tmp, " "); if ($tmp_int) $str_tmp=substr($str_tmp, 0, $tmp_int); // extracts numerical value of LIMIT if ($str_tmp>$SQL['MaxResults']) $SQL['SQL']=str_replace(" LIMIT ".$str_tmp, " LIMIT ".$SQL['MaxResults'], $SQL['SQL']); // if submitted limit is over $SQL['MaxResults'] replaces it with $SQL['MaxResults'] } else $SQL['SQL'].=" LIMIT ".$SQL['MaxResults']; } } if ($SQL['Status_Query']==1) // user requested SQL be run, will validate it {foreach ($arr_Invalids as $tmp_str) if (strpos(strtoupper($SQL['SQL']), $tmp_str)>0) $str_Invalid=$tmp_str; if (strlen($str_Invalid)>0) {$SQL['Status_Query']=2; $SQL['Error_Query']="Invalid-".$str_Invalid; } } if ($SQL['Status_Query']==1) // query passes pre-checks will execute {$SQL['Error_Query']="Valid"; $SQL['Query']=mysqli_query(DB(), $SQL['SQL']); $str_tmp=mysqli_error(DB()); if (strlen($str_tmp)>1) {$SQL['Status_Query']=2; $SQL['Error_Query']=$str_tmp; } } } if ($SQL['Status_Query']==1) // query successfully run will load results into $SQL['Result'] {$SQL['Rows']=mysqli_num_rows($SQL['Query']); if ($SQL['Rows']>0) while ($r = mysqli_fetch_assoc($SQL['Query'])) {if (count($SQL['Result'])==0) $SQL['Result'][]= array_keys($r); $SQL['Result'][]=$r; } } // ++++++++++++++++++ DATA PROCESSING ABOVE ++++++++++++++++++ // ++++++++++++++++++ PAGE OUTPUT BELOW ++++++++++++++++++ ?> <!DOCTYPE html> <html><head><title>Simple MySQL Query Tool</title> <link rel="stylesheet" type="text/css" href="https://www.rainbowspuppiessunshine.com/tools/dbtest/dbtest.css"> </head>   <body> <a href="https://www.rainbowspuppiessunshine.com" style="text-decoration: none; color: #999999;"> <img src="https://www.rainbowspuppiessunshine.com/layout/rpslogo.gif" alt="Rainbows Puppies & Sunshine" border="0"> </a> <div class="header_MenuBar"> <a href="https://www.rainbowspuppiessunshine.com/tools/dbtest/" class="header_Link">MySQL Tool</a> </div>   <table border="0" width="100%"><tr> <td width="50%" valign="top" style="border-right: 4px solid #CCCC99; padding: 0px 10px 0px 10px;"> <h1>Database Status</h1><hr class="h1"> <p class="main_Explain">This tool uses MySQLi (<a href="https://www.php.net/manual/en/book.mysqli.php" target="mysqli">https://www.php.net/manual/en/book.mysqli.php</a>) to connnect and interact with a MySQL database. The below area details the specifics of this particular connection and any query that was run. <?php echo html_ConnectionStatus($SQL['Error_Connect'], $SQL['Error_Query']); ?> </td>   <td width="50%" valign="top" style="padding: 0px 10px 0px 10px;"> <?php echo show_Area("Tool", $SQL['Status_Connect']); ?> <h1>Query Tool</h1><hr class="h1"> <p class="main_Explain">Use the below area to execute a SELECT query against the database. <form action="<?php echo $_SERVER['SCRIPT_URI']; ?>#results" method="post"> <p><table align="center" cellspacing="0" cellpadding="0" border="0" class="form_Table"><tr> <td class="form_VCell">SQL:</td> <td class="form_VCell"> <textarea style="width: 420px; height: 110px; font: 14px Verdana; "name="in_SQL"><?php echo $SQL['SQL'];?></textarea></td> </tr></table> <p align="center"><input type="submit" class="form_Button" value="Run Query"> </form> <br><p class="main_Explain">Or select a table of the database to view its structure. <form action="<?php echo $_SERVER['SCRIPT_URI']; ?>#results" method="get"> <p><table align="center" cellspacing="0" cellpadding="0" border="0" class="form_Table"><tr> <td class="form_VCell">Tables:</td> <td class="form_VCell"><select style="font: 12px Arial" name="in_Table"> <?php foreach ($SQL['Tables'] as $tmp_table) echo "<option value=\"".$tmp_table."\">".$tmp_table."</option>"; ?> </select></td> </tr></table> <p align="center"><input type="submit" class="form_Button" value="Get Structure"> </form></div> </td></tr></table>   <?php echo show_Area("Results", $SQL['Status_Query']); ?> <hr style="height: 4px; background-color: #CCCC99; margin-top: -8px;"> <a id="results"><h1>Query Results</h1><hr class="h1"></a> <p class="main_Explain">Below is the SQL submitted to the database and then the results of that query. This tool automatically limits any query to just <?php echo $SQL['MaxResults'] ?> results which is why a LIMIT appears in the SQL even if one was not submitted. That can be modified by changing the $SQL['MaxResults'] value in the Variables area of this script. <p style="text-align: center; font-weight: 900;"> <?php echo $SQL['SQL'].";<p align=\"center\">".html_Results($SQL['Result']); ?> </div> <?php echo show_Area("Status", $SQL['Status_Query']); ?> <hr style="height: 4px; background-color: #CCCC99; margin-top: -8px;"> <a id="status"><h1>Connection Data</h1><hr class="h1"></a> <p class="main_Explain">Below is all the connection meta-data returned by various MySQLi diagnostic functions. Each function below also serves as a link to a help page for that function. <p style="text-align: center; font-weight: 900;"> <?php echo "<p align=\"center\">".html_Results($SQL['Result']); ?> </div>   <div class="footer_Line"> <a href="https://www.rainbowspuppiessunshine.com">Rainbows Puppies & Sunshine</a> </div> </body></html>

The functionality of this section or any other section of this site can be customized to your needs. Contact Us and let us know how we can help.