SQL Query script

[igorw]

If you're developing with phpBB3, you often need to do queries. It's a pain having to go to phpmyadmin each time, so here's sql.php, just drop it in your forum root (note that it's a potential security risk though):

Code: Select all

<?php
/**
 * @package phpBB3
 * @version 1.0.0
 * @copyright (c) 2007 eviL3
 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
 */

/**
* @ignore
*/
define('IN_PHPBB', true);
$phpbb_root_path = './';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);

// Start session management
$user->session_begin();
$auth->acl($user->data);
$user->setup();

// Check if the user is logged in and an admin
if (!$auth->acl_get('a_'))
{
   if ($user->data['user_id'] != ANONYMOUS)
   {
      redirect(append_sid($phpbb_root_path . "index.$phpEx"));
   }
   
   login_box("{$phpbb_root_path}sql.$phpEx");
}

$submit      = request_var('submit', false);
$sql_data   = request_var('sql_data', '');

if ($submit && $sql_data)
{
   $sql_ary = str_replace("\n", ' ', $sql_data);
   $sql_data = str_replace('phpbb_', $table_prefix, $sql_data);
   $sql_ary = explode(';', $sql_data);
   
   // Loop through our sql queries
   for ($i = 0, $size = sizeof($sql_ary); $i < $size; $i++)
   {
      $db->sql_query($sql_ary[$i]);
   }
   
   $message = 'Queries executed successfully!';
   $message .= '<br /><br />';
   $message .= sprintf('%sClick here to return to the query form%s', '<a href="' . append_sid($phpbb_root_path . 'sql.' . $phpEx) . '">', '</a>');
   
   trigger_error($message);
}

?>
<form action="<?php echo append_sid($phpbb_root_path . 'sql.' . $phpEx); ?>" method="post">
   <textarea style="width: 80%; height: 200px;" name="sql_data"></textarea><br />
   <input type="submit" name="submit" value="<?php echo $user->lang['SUBMIT']; ?>" />
</form>

You need to be logged in as an admin to use it. Just go to sql.php and a textarea should appear.


Thanks to Lord Le Brand for poiting out a little problem.

[blazes816]

What was the problem? You were accidentally appending 'TRUNCATE *;' to the query?

[igorw]

What was the problem? You were accidentally appending 'TRUNCATE *;' to the query?

LOL! No

This:

Code: Select all

$sql_ary = str_replace("\n", ' ', $sql_data);

Was:

Code: Select all

$sql_ary = str_replace("\n", '', $sql_data);

This would create a problem if somebody entered a query like:

Code: Select all

SELECT user_id
FROM phpbb_users

As it would result in:

Code: Select all

SELECT user_idFROM phpbb_users

[bbjimbb]

Maybe a bad question.

But how does this thing know what query I need? :\

[Mike TUMS]

Maybe a bad question.

But how does this thing know what query I need? :\

Maybe cause you write query by yourself?

[bbjimbb]

How? (In code with this thing)

[Mike TUMS]

just drop it in your forum root (note that it's a potential security risk though):
You need to be logged in as an admin to use it. Just go to sql.php and a textarea should appear.

that's how...

[bbjimbb]

:O Nice! :checkyes:
Wrong from me! :thumbsdown: :checkno:
I was thinking it going automaticly if I go to that page.

Solved! :checkyes:

[Whiskeyjack]

could you package this up into a module for the admin cp? that'd be quite handy, and much more secure.

[igorw]

I wrote this a while ago, i don't know how well it works though...

http://phpbbmodders.net/mods/temp/acp_s ... _0.1.0.zip

[Whiskeyjack]

thanks a lot, haven't test any SQL in it yet, but it installed just fine.

[Obsidian]

I wrote this a while ago, i don't know how well it works though...

http://phpbbmodders.net/mods/temp/acp_s ... _0.1.0.zip

That should have been default.

Along with maybe having a way to optimize the tables within phpBB on demand.

Anyways, I might install this one on one of my boards.

EDIT: File is missing.

[igorw]

Whoopsies, fixed. I think i need to update this script some time.

[Kamahl]

wow, very useful mod, it saves a lot of time

[Obsidian]

Hey, evil, does this display a success/error message upon submit, or do we just get the general error of doom page if something is incorrect?