Random MySQL Queries/Procedures

[alexander]

Random collection of queries, post cool things you do with that excuse of a language (i likes mysql, i hate the language)

 

ever needed to check grants for all the users on the system in a database? i did, and found that i had to do it by hand, so i said screw all that and wrote a procedure to do that for me...

 

DELIMITER // # this changes the line delimiter (this is so the whole procedure gets stored and mysql doesnt try to exec every line throwing errors back)
DROP PROCEDURE IF EXISTS check_grants//

CREATE PROCEDURE check_grants()
BEGIN
   SET @i = 0; # its an iterator
   SELECT count(*) INTO @max FROM mysql.user WHERE user<>""; #this gets us the amount of non empty users in the users table
   label1: LOOP # start loop
       IF (@i >= @max) THEN  # create an exit condition
           LEAVE label1;
       END IF;

       # because sql (in this case MySQL is a sucky language, to get it to do something dynamic, you are forced to use a prepare statement with later instructing mysql to replace "?"s by a variable. 
       # so this generates a query that  shows grants for a user that is generated by another query that does a limited select on the mysql users table, and formats the output in 'user'@'host' format

       PREPARE test1 FROM "set @q = (select (concat("show grants for ",(concat_ws("@",concat("'",User,"'"),concat("'",Host,"'"))),";")) from mysql.user where user <> "" LIMIT ?,1)"; 
       EXECUTE test1 USING @i; # executes the above statement selecting i'th result
       DEALLOCATE PREPARE test1; # cleans up test1

       SET @i = @i + 1; # increments iterator

       PREPARE test1 from @q; # prepares the statement generated by the previous query
       EXECUTE test1; # executes query
       DEALLOCATE PREPARE test1; # cleans up test1 once again

       ITERATE label1; # goes back to the label above
   END LOOP label1;
END//
DELIMITER ; # changes the delimiter back, so we can use mysql normally again

[Pyrotex]

First, let's format this so it can be read by sixty-year-old ex-programmers with cataracts:

[font="Courier New"][b]delimiter //
DROP PROCEDURE IF EXISTS check_grants//

CREATE PROCEDURE check_grants()
BEGIN
   set @i = 0;
   select count(*) into @max from mysql.user where user<>"";
   label1: LOOP
       IF (@i >= @max) THEN 
           LEAVE label1;
       END IF;

       prepare test1 from "set @q = 
         (select 
           (concat
             ("show grants for ",
               (concat_ws
                 ("@",concat
                   ("'",User,"'"),
                   concat("'",Host,"'")
                 )
               ),";"
             )
           ) 
         from mysql.user 
         where user <> "" LIMIT ?,1)";

       execute test1 using @i;
       deallocate PREPARE test1;
       PREPARE test1 from @q;
       SET @i = @i + 1;
       EXECUTE test1;
       DEALLOCATE PREPARE test1;
       ITERATE label1; 
   END LOOP label1;
END//
delimiter ;[/b][/font]

Okay, that looks better. And now it's clear (mostly) what you're doing.

What language are you using? One of those UNIX scripting languages???

It's sure not as clean as Perl or Python.

And [ahem!] where did you comment your variables????? :evil:

 

Getting those parentheses to balance was a royal pain. And of course, I don't know if the language will allow me to do that and still have it parse correctly. :) Still, it was the only way I could even begin to read that spagetti soup.

 

Good job. Nice useful piece of code you can carry around on a memory stick! thanks!

P

[alexander]

The language is that of Mordor, MySQL

 

no comments on variables, they are global...

[alexander]

Quick and dirty way to check if there is an issue with some table replication in a master-slave setup (which i have seen with no errors on the mysql side what-so-ever)

 

Yeah some day i'll be releasing the framework that some of this code will be a part of. For now, here is my mysql class, (yeah i am going to be making another version in the near future), and neutered message class (it's, uh, missing a few things you can see it does in the config, but its missing it because those pieces of framework are not there yet), and the code for checking, which is pretty stupid simple. Lets hope this helps someone :eek:

 

Note, the code is all GPLed, so you are more then welcome to use it and change it, but if you do use it, dont forget to give proper credit, please...

 

Also, don't give me a "its not overly commented, you should blah blah blah", comments are not free in a scripting language, and well-formatted code tends to speak for itself, if you dont know what it does, then you need to learn some fundamentals and this code is not really meant for you...

 

mysql_class.php

<?php

 // Class dealing with various MySQL needs

class MySQL
{
 
//Stuff that nobody should be able to see/use but this class
private $host;			   // mySQL host to connect to
private $user;			   // mySQL user name
private $pw;				 // mySQL password
private $db;				 // mySQL database to select
private $dbLink;			// current/last database link identifier
private $lastQuery;		 // holds the last query executed.
 
// Variables that dont really matter and if someone wanted to change them/use them/extend using them, by all means 
public $lastError;		 // holds the last error. Usually mysql_error()
public $autoSlashes;	   // the class will add/strip slashes when it can
 
function __construct($host=NULL, $user=NULL, $pw=NULL, $db=NULL, $autoSlashes=true) 
{
	$this->host=$host;
	$this->user=$user;
	$this->pw=$pw;
	$this->db=$db;
	$this->autoSlashes = ($autoSlashes===false) ? false : true;
}
 
private function getRow($result=NULL, $type="MYSQL_BOTH")  // internal use function only
{
	if(!$result) throw new Exception ("You need a query result to get a row from it");
	switch(strtoupper($type))
	{
		case "MYSQL_ASSOC":
			$row = mysql_fetch_array($result, MYSQL_ASSOC);
			break;
		case "MYSQL_NUM":
			$row = mysql_fetch_array($result, MYSQL_NUM);
			break;
		default:
			$row = mysql_fetch_array($result, MYSQL_BOTH); 
	}
	if (!$row) return false; //necessary evil for function use
	if ($this->autoSlashes) foreach ($row as $key => $value) { $row[$key] = stripslashes($value); } // strip all slashes out of row...
	return $row;
}
function db_connect($persistent=true) 
  {
	$this->dbLink = ($persistent) ?  mysql_pconnect($this->host, $this->user, $this->pw) : $this->dbLink = mysql_connect($this->host, $this->user, $this->pw);
	// Do some securifying, clear the data so it cant be dumped
	$this->host="";
	$this->user="";
	$this->pw="";
	if(!$this->dbLink)
	{
		$this->lastError = mysql_error();
		throw new Exception("MySQL Database connection failed");
	}
	if($this->db)
	{
		try { $this->db_select(); }
		catch(Exception $err){ throw new Exception($err->getMessage()); } //basically just pass on the exception
	}
	$this->db='';
	return;  // success
}
function db_close() 
{
	if(!@mysql_close($this->link_id))
	{
		$this->lastError = mysql_error();
		throw new Exception("MySQL database disconnect failed");
	}
	return;
}
function db_select($db=NULL) 
{ 
	if($db) $this->db = $db; 
	if(!$db)
	if (!mysql_select_db($this->db)) 
	{
		$this->lastError = mysql_error();
		throw new Exception("MySQL database selection failed");
	}
	return;
}
function query($sql=NULL) 
{		
	if(!$sql) throw new Exception("Query failed, no query was passed");
	$this->lastQuery = $sql;
	$r = mysql_query($sql);
	if(!$r) 
	{
		if (!($this->lastError = mysql_error())) return $r; // This basically insures us that we can return a bool, a false or a NULL from the database as a valid result
		else throw new Exception("MySQL query failed"); 
	}
	return $r;
}
function select($sql=NULL)
{
	if(!preg_match("/^s*(select){1,}/i", $sql)) throw new Exception("The query does not look like it's a SELECT statement");
	try{ $r = $this->query($sql); }
	catch(Exception $err){ throw new Exception($err->getMessage()); }
	return $r;
}
function selectOne($sql=NULL) 
{	
	try{ $r = $this->query($sql); }
	catch(Exception $err){ throw new Exception($err->getMessage()); }
	if(mysql_num_rows($r) > 1) throw new Exception ("The query returned more that one result");
	$ret = mysql_result($r, 0);
	if($this->autoSlashes) stripslashes($ret);
	$this->free($r);
	return $ret;
}
function getLastQuery(){ return $this->lastQuery; }
function insert($sql=NULL, $id=FALSE) 
{
	if(!preg_match("/^s*(insert){1,}/i", $sql)) throw new Exception("The query does not look like it's an insert statement");
	try 
	{ 
		$r = $this->query($sql); 
		if($id) $id = $this->selectOne("SELECT LAST_INSERT_ID()");
	}
	catch(Exception $err){ throw new Exception($err->getMessage()); }
	return ($id) ? $id : NULL;
}
function update($sql=NULL) 
{
	if(!preg_match("/^s*(update){1,}/i", $sql)) throw new Exception("This query does not look like it's an UPDATE statement");
	try{ $this->query($sql); }
	catch(Exception $err){ throw new Exception($err->getMessage()); }
	return mysql_affected_rows(); //since we don't really care whether rows were updated or not, but the end user should know, just return how many rows were updated
}
function selectArray($sql=NULL, $type="MYSQL_BOTH")
{		
	try{ $r = $this->select($sql); }// Run the select query and get a return id back 
	catch(Exception $err){ throw new Exception($err->getMessage()); }
	if(mysql_num_rows($r)<1)throw new Exception("No results were returned");
	while($row = $this->getRow($r, $type)){	$ret[]=$row; } //populate return array
	$this->free($r); // Free the original result set
	return $ret;
}
function queryArray($sql=NULL, $type="MYSQL_BOTH")
{
	try{ $r = $this->query($sql); }// Run the select query and get a return id back 
	catch(Exception $err){ throw new Exception($err->getMessage()); }
	if(mysql_num_rows($r)<1)throw new Exception("No results were returned");
	while($row = $this->getRow($r, $type)){	$ret[]=$row; } //populate return array
	$this->free($r); // Free the original result set
	return $ret;
}
function getLastError($show_query=false) 
{
	$r = $this->lastError;
	if ($show_query && (!empty($this->lastQuery))) $r.="{%r}".$this->lastQuery; //this way we can split it again later for display
	return $r; 
}
function escape($var=NULL)
{
	if(empty($var))	throw new Exception("Empty variable can not be escaped");
	if(!$this->dbLink) throw new Exception("Database link has not yet been established");
	if(get_magic_quotes_runtime()) $var = stripslashes($var);
	return mysql_real_escape_string($var, $this->dbLink);
}
function free($result=NULL) 
{
	return (!@mysql_free_result($result)) ? false : true;
}
} 
?>

message_class.php

<?php 
require_once "defcon_config.php";
class Message
{
protected $err;
protected $obj;
function __construct($message,$code=5,$file=NULL,$line=NULL,&$obj=NULL)
{
	$this->obj =& $obj; //this is really only used if we do defcon 5, and only for email
	if(is_string($message)) 
	{
		$this->err['message']=$message;
		$this->err['code']=$code;
		$this->err['file']=$file;
		$this->err['line']=$line;
	}
	else
	{
		$this->err['message']=$message->getMessage();
		$this->err['code']=$message->getCode();
		$this->err['file']=$message->getFile();
		$this->err['line']=$message->getLine();
	}
}

function __toString()
{
	switch($this->code)
	{
		case 3:
			return $this->printMsg(ERROR_CLASS);
			break;
		case 2:
			return $this->printMsg(WARNING_CLASS);
			break;
		default:
			return $this->printMsg();
	}
}
private function printMsg($class=MESSAGE_CLASS)
{
	$html.="<div class='{$class}'>";
	if(isset($this->err['message'])) $html.=htmlentities($this->err['message'])." <br/>";
	$html.="</div>";
	return $html;
}
}
?>

defcon_config.php

<?php
//DEFCON5 - Normal alert operational message
define("MESSAGE_CLASS", "message");

//DEFCON4 - Normal alert warning condition
define("WARNING_CLASS", "warning");

//DEFCON3 - Increased alert, Error level 1 {error message}
define("ERROR_CLASS","error");

//DEFCON2 - Increased alert, log condition, Error level 2, {Error level 1 + line, file} 
define("LOG_FILE","/var/log/www/mysite_error.log");

//note that you can log to a database, or file, or both, just uncomment the following lines and give them proper values
/*
* define("LOG_DATABASE", "db");
* define("LOG_TABLE", "dbtable");
* define("LOG_USER", "dbuser");
* define("LOG_HOST", "host");
* define("LOG_PASS", "secret_pass");
*/

//DEFCON1 - Maximum alert, log condition, email the admin, Error level max, {error level 2 + print_r of the class throwing it}
define("ADMIN_EMAIL","[email protected]");
define("ADMIN_SUBJECT_PREAMBLE", "Web_");
?>

Checker:

<?php
require_once "mysql_class.php";
require_once "message_class.php";

$user="user";
$password="pass";

$servers = array("10.10.10.10", "10.10.10.11", "10.10.10.12");
$check_tables = array("db1.table1", "db1.table2", "db2.table1", "db3.table1");

$links=array();
try
{
foreach($servers as $server)
{
	$links[]=new MySQL($server, $user, $password);
}
}
catch(Exception $err)
{
echo new Message($err, 3);
die();
}
$check=array();
$i=0;
foreach($links as $link)
{
$query="CHECKSUM TABLE ";
foreach($check_tables as $table)
{
	$query.=$table.", ";
}
$query = substr($query, 0, -2).";";
try
{
	$link->db_connect();
	$tmps=$link->queryArray($query);
}
catch(Exception $err)
{
	echo new Message($err, 3);
	die();
}
$ii=0;
foreach($tmps as $tmp)
{
	$check[$i][$ii++]=$tmp[1];
}
$i++;
}

$clean=true;
for($i=0; $i<=count($check); $i++)
{
if(!$check[$i+1])
{
	break;
}
for($ii=0; $ii<=count($check[$i]); $ii++)
{
	if($check[$i][$ii]!=$check[$i+1][$ii])
	{
		echo "Server ".$servers[$i]." table ".$check_tables[$ii]." does not match the checksum of ".$servers[$i+1]." table ".$check_tables[$ii]."<br/>";
		$clean=false;
	}
}
}
if($clean)
{
echo "Yeah, everything looks pretty good for now :eek_big: ";
}


?>

[Pyrotex]

Beautiful code. And nicely commented. Kudos.