I already read that our only possibility to connect to the database is to use phpMyAdmin, so we can’t use external tools. But my question is, if I’m allowed to create a PHP interface acceping a mysql statement and a password for instance, so that I can build my own external tools?
sorry to open this old post, but i wanted to know if you solved the problem yo had and if you did , how? thanks
I never really had a problem. I built a PHP script taking a password and a SQL query/statement. The script uses the password in mysql_connect to authenticate the user (me) and then runs the input query and shows the result. I haven’t got this script anymore, but it wouldn’t take long rewriting it.
bplaced does not allow for external tools to connect to the database for security reasons, which made me think that building a script bypassing this security might not be allowed too, but I never got an answer to that question.
Just in case, someone needs it, I just rebuilt this database page. Just fill in your database name and database user name and it should work. It uses UTF-8 as default for all queries as well as page charset. I tested this script with Apache 2.4.16 and the page’s style works in Chrome and IE. Here’s the code, feel free to use it and to change it to fit your needs:
Edit: I just added a field for the user name (instead of a hardcoded user name) and made the table width not restricted to the body, so large tables don’t shrink too much (But I didn’t test, how this works, if a column contains a very large string).
<?php
define('CURRENT_FILE', basename(__FILE__));
define('DB_DATABASE_NAME', 'DATABASENAME'); // <- DATABASE NAME HERE
define('DB_HOST', 'localhost'); // <- HOST NAME HERE
define('NAME_LOGININDICATOR', 'login');
define('NAME_PASSWORDBOX', 'pw');
define('NAME_QUERYBOX', 'query');
define('NAME_USERNAMEBOX', 'user');
function formatData ($value) {
if ($value === null)
return 'NULL';
else if (is_bool($value))
return $value ? 'FALSE' : 'TRUE';
else
return $value;
}
?>
<html>
<head>
<title>DB</title>
<meta charset="utf-8" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style rel="stylesheet">
#resultTable {
background-color: #e3eeff;
border-collapse: collapse;
}
#resultTable td, #resultTable th {
border: solid 3px #fff;
padding: 2px 5px;
white-space: nowrap;
}
#resultTable tr:nth-child(even) td {
background-color: #c4dBff;
}
#resultTable tr:hover td {
background-color: #96ebd9;
}
#resultTable tr td:hover {
background-color: #63d6be;
}
#resultTable th {
background-color: #9ec4ff;
}
</style>
</head>
<body style="text-align: center; ">
<div style="display: inline-block; ">
<?php
// Validate inputs and authenticate user
$authenticated = false;
$db = null;
$loginError = null;
$query = @$_REQUEST[NAME_QUERYBOX] ?: null; // Includes POST and REQUEST
$queryResult = null;
$userName = @$_REQUEST[NAME_USERNAMEBOX] ?: null;
if (isset($_POST[NAME_LOGININDICATOR])) {
// Check credentials
$db = @new mysqli(DB_HOST, $userName, @$_POST[NAME_PASSWORDBOX] ?: '', DB_DATABASE_NAME);
if (!$db->connect_errno) {
// Validate query
$db->set_charset('utf8');
if ($query !== null && strlen($query = trim($query)) > 0) {
$queryResult = @$db->query($query);
if (!$db->errno)
$authenticated = true;
else
$loginError = "Query error #{$db->errno}: {$db->error}";
} else {
$loginError = 'Warning: Empty query';
}
} else {
$loginError = "Login error #{$db->connect_errno}: {$db->connect_error}";
@$db->close(); // "@" for suppressing "Warning: mysqli::close(): Couldn't fetch mysqli in ..."
}
}
// Generate page
if ($authenticated) {
// Render result page
?>
<div style="text-align: left; ">
<a href="<?= sprintf('./%s?%s=%s&%s=%s', CURRENT_FILE, NAME_USERNAMEBOX, $userName, NAME_QUERYBOX, htmlspecialchars($query)); ?>">Go back</a>
<p><?= $db->affected_rows ?: 'No'; ?> rows affected</p>
</div>
<?php
if (is_bool($queryResult)) {
echo '<p>Query ' . ($queryResult ? 'successful' : 'failed') . '</p>';
} else {
echo '<table id="resultTable">';
$firstRow = true;
foreach ($queryResult as $row) {
if ($firstRow) {
// Header row (column names)
$firstRow = false;
echo '<tr>';
foreach ($row as $columnName => $value)
echo "<th>$columnName</th>\n";
echo '</tr>';
}
// Data rows
echo '<tr>';
foreach ($row as $columnName => $value)
echo '<td>' . formatData($value) . '</td>';
echo '</tr>';
}
echo '</table>';
}
$db->close();
?>
<div style="text-align: left; ">
<a href="<?= sprintf('./%s?%s=%s&%s=%s', CURRENT_FILE, NAME_USERNAMEBOX, $userName, NAME_QUERYBOX, htmlspecialchars($query)); ?>">Go back</a>
</div>
<?php
} else {
// Show login form
if ($loginError !== null)
echo "<p style=\"color: #f00; \">$loginError</p>";
?>
<h1>Authentication</h1>
<form action="./<?= CURRENT_FILE; ?>" method="post">
<table>
<tr>
<td>Username:</td>
<td>
<input name="<?= NAME_USERNAMEBOX; ?>" style="width: 100%; " type="text" value="<?= $userName; ?>" />
</td>
</tr>
<tr>
<td>Password:</td>
<td>
<input name="<?= NAME_PASSWORDBOX; ?>" style="width: 100%; " type="password" />
</td>
</tr>
<tr>
<td style="vertical-align: top; ">
Query/<br />
Statement:
</td>
<td>
<textarea cols="100" name="<?= NAME_QUERYBOX; ?>" rows="20"><?= $query ?: ''; ?></textarea>
</td>
</tr>
<tr>
<td> </td>
<td>
<input style="margin-top: 20px; padding: 15px; width: 100%; " type="submit" value="Authenticate" />
</td>
</tr>
</table>
<input name="<?= NAME_LOGININDICATOR; ?>" type="hidden" />
</form>
<?php
}
?>
</div>
</body>
</html>