SQL Query script

Tools by the phpBBModders team and Community contributed tools.
Forum rules
Only post tools related to modding in here, simple.
User avatar
igorw
Past Contributor
Past Contributor
Posts: 1967
Joined: 01 Jun 2006, 20:48
Real name: Igor

SQL Query script

Postby igorw » 08 Apr 2007, 19:07

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
Past Contributor
Past Contributor
Posts: 187
Joined: 07 Oct 2006, 03:00
Real name: Tyler
Location: Wichita, Kansas

Postby blazes816 » 09 Apr 2007, 01:16

What was the problem? You were accidentally appending 'TRUNCATE *;' to the query?
User Number 9e071a3a594a8964cbefe784f8a6afaa94c0de17
My MODs: http://github.com/blazes816/MODs

User avatar
igorw
Past Contributor
Past Contributor
Posts: 1967
Joined: 01 Jun 2006, 20:48
Real name: Igor

Postby igorw » 09 Apr 2007, 14:08

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


LOL! No :lol:

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
New member
New member
Posts: 6
Joined: 22 Jan 2008, 06:43

Re: SQL Query script

Postby bbjimbb » 21 Feb 2008, 19:16

Maybe a bad question.

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

User avatar
Mike TUMS
Member
Member
Posts: 162
Joined: 16 Jan 2007, 05:51
Real name: Mihail
Location: Moscow, RU

Re: SQL Query script

Postby Mike TUMS » 21 Feb 2008, 19:22

bbjimbb wrote:Maybe a bad question.

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

Maybe cause you write query by yourself? :P
Image
Introduction forum's welcome bot. Guru of Indian-style coding .
Russian spy. Master of copypasta. Google's right hand.
Advanced lamer of Modders.


May translate your MOD to Russian.

bbjimbb
New member
New member
Posts: 6
Joined: 22 Jan 2008, 06:43

Re: SQL Query script

Postby bbjimbb » 21 Feb 2008, 19:23

How? (In code with this thing)

User avatar
Mike TUMS
Member
Member
Posts: 162
Joined: 16 Jan 2007, 05:51
Real name: Mihail
Location: Moscow, RU

Re: SQL Query script

Postby Mike TUMS » 21 Feb 2008, 19:27

eviL<3 wrote: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...
Image
Introduction forum's welcome bot. Guru of Indian-style coding .
Russian spy. Master of copypasta. Google's right hand.
Advanced lamer of Modders.


May translate your MOD to Russian.

bbjimbb
New member
New member
Posts: 6
Joined: 22 Jan 2008, 06:43

Re: SQL Query script

Postby bbjimbb » 21 Feb 2008, 19:29

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

Solved! :checkyes:

Whiskeyjack
New member
New member
Posts: 5
Joined: 30 May 2007, 07:51

Re: SQL Query script

Postby Whiskeyjack » 27 Feb 2008, 11:50

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

User avatar
igorw
Past Contributor
Past Contributor
Posts: 1967
Joined: 01 Jun 2006, 20:48
Real name: Igor

Re: SQL Query script

Postby igorw » 27 Feb 2008, 15:58

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
New member
New member
Posts: 5
Joined: 30 May 2007, 07:51

Re: SQL Query script

Postby Whiskeyjack » 28 Feb 2008, 11:09

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

User avatar
Obsidian
Supporter
Supporter
Posts: 736
Joined: 13 May 2008, 15:20
Real name: Damian

Re: SQL Query script

Postby Obsidian » 02 Jun 2008, 15:52

eviL<3 wrote: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. :cry:

User avatar
igorw
Past Contributor
Past Contributor
Posts: 1967
Joined: 01 Jun 2006, 20:48
Real name: Igor

Re: SQL Query script

Postby igorw » 02 Jun 2008, 15:56

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

User avatar
Kamahl
Member
Member
Posts: 135
Joined: 02 Jan 2008, 13:08
Real name: Martin
Location: Slovakia

Re: SQL Query script

Postby Kamahl » 03 Jun 2008, 12:25

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

User avatar
Obsidian
Supporter
Supporter
Posts: 736
Joined: 13 May 2008, 15:20
Real name: Damian

Re: SQL Query script

Postby Obsidian » 03 Jun 2008, 12:59

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?


Return to “Tools for MOD authors”

Who is online

Users browsing this forum: CommonCrawl [Bot] and 0 guests