Extern MySQL connection?


#1

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?


#2

sorry to open this old post, but i wanted to know if you solved the problem yo had and if you did , how? thanks


#3

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.


#4

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>&nbsp;</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>