sql.php

Go to the documentation of this file.

<?php

require_once 'const.php';
require_once('config.php');
require_once('error.php');


# SQL queries


$sql_actions = array(

'select' => array (
        "sql" => "SELECT @v:columns@ FROM @v:tables@ @v:join@ WHERE @v:cond@ @v:group_by@ ORDER BY @v:order@ ",
        "kw_default" => array( "columns" => "*", "cond" => "1 = 1", "order" => "id", "join" => "", "group_by" => "" ),
        "kw_args" => array ( "tables" )
        ),

'update' => array (
        "sql" => "UPDATE @v:tables@ SET @kvq:data@ WHERE @v:cond@",
        "kw_default" => array( "cond" => "1 = 1" ),
        "kw_args" => array ( "cond", "tables", "data" )
),

'insert' => array (
        "sql" => "INSERT INTO @v:table@ ( @k:data@ ) VALUES ( @vq:data@ )",
        "kw_default" => array(),
        "kw_args" => array ( "table", "data" )
),

'delete' => array (
        "sql" => "DELETE FROM @v:table@ WHERE @v:cond@",
        "kw_default" => array( "cond" => "1 = 1" ),
        "kw_args" => array ( "table", "cond" )
),

'check_pw' => array (
        "sql" => "SELECT user.*,role.name AS role_name,degree.description as degree_description FROM user,state,role,degree WHERE degree.id=user.degree_id AND state.name='active' AND user.state_id = state.id AND user.login = @vq:login@ AND user.password = @vq:password@ AND user.role_id = role.id LIMIT 1",
        "kw_default" => array( ),
        "kw_args" => array ( "login", "password" )
        ),

'set_state' => array (
        "sql" => "UPDATE @v:table@, state SET @v:table@.last_state_change = NOW(),  @v:table@.state_id = state.id WHERE state.name = @vq:state@ AND @v:table@.id = @vq:id@ AND @v:cond@ AND @v:table@.state_id != state.id ",
        "kw_default" => array( "cond" => "1 = 1" ),
        "kw_args" => array ( "state", "table", "id" )
),

'last_id' => array (
        "sql" => "SELECT last_insert_id() AS last_id",
        "kw_default" => array(),
        "kw_args" => array ()
),

'get_uid' => array (
        "sql" => "SELECT id from user WHERE email = @vq:email@",
        "kw_default" => array(),
        "kw_args" => array ()
),

);


function sql_quote($value, $dblink ) {

   # Strip slashes

   if (get_magic_quotes_gpc()) {
       $value = stripslashes($value);
   }

   $type = gettype($value);

   switch($type) {
        
        case "string":
             $value = "'" . mysql_real_escape_string($value, $dblink) . "'";
             break;

        case "boolean":
             $value = ($value) ? 1 : 0; # xxx mysql specific??
             break;

        case "integer": 
        case "float": 
             break;
        
        default:
                user_error("unsupported type: $type", E_USER_ERROR);
    }

    return $value;
}


function sql_kw_parse ( $action, $kw, $dblink ) {

        global $sql_actions;

        # check arguments
 
        if (!isset($sql_actions[$action])) {
                user_error("illegal action: $action", E_USER_ERROR);
        }

        if (!is_array ($kw)) {
                user_error("illegal parameter type:" . gettype($kw), 
                           E_USER_ERROR);
        }

        $kw = array_merge($sql_actions[$action]['kw_default'], $kw);


        foreach ($sql_actions[$action]['kw_args'] as $k) {
                if (!isset($kw[$k])){ 
                        user_error("required keyword missing: $k", 
                        E_USER_ERROR);
                }
        }
                
        $kw_out = array();
 
        foreach ($kw as $key => $val) {

                $type = gettype($val);

                switch($type) {

                case "array":

                        $keys = $vals = $keys_vals = "";
                        $vals_q = $keys_vals_q = "";

                        foreach ($val as $k => $v ) {
                                
                                $keys .= "$k,";

                                # unquoted 

                                $vals .= "$v,";
                                $keys_vals .= "$k = $v,";

                                $kw_out["@v:${key}#${k}@"] = $v;

                                # quoted 

                                $v = sql_quote($v, $dblink);

                                $vals_q .= "$v,";
                                $keys_vals_q .= "$k = $v,";

                                $kw_out["@vq:${key}#${k}@"] = $v;
                        }

                        $kw_out["@k:$key@"] = rtrim($keys, ',');
                        $kw_out["@v:$key@"] = rtrim($vals, ',');
                        $kw_out["@kv:$key@"] = rtrim($keys_vals, ',');

                        $kw_out["@vq:$key@"] = rtrim($vals_q, ',');
                        $kw_out["@kvq:$key@"] = rtrim($keys_vals_q, ',');

                        break;

                case "string": 

                        $kw_out['@v:' . $key . '@'] = $val;
                        $kw_out['@vq:' . $key . '@'] = sql_quote($val, $dblink);
                        break;

                default:
                        user_error("illegal type $type for key $key",  
                                   E_USER_ERROR);
                }
        }
 
        return $kw_out;
}


function sql_query ( $action, $kw, $dblink ) {

        global $sql_actions, $debug_level;



        if (!isset($sql_actions[$action])) {
                user_error("illegal action: $action" , E_USER_ERROR);
        }

        if ($debug_level > 10) {
                print "action: $action";
                print "<hr><pre>SQL Keywords before parsing:\n";
                print_r($kw);
                print "</pre><hr>";
        }       

        $kw = sql_kw_parse($action, $kw, $dblink);

        if ($debug_level > 10) {
                print "<hr><pre>SQL Keywords after parsing:\n";
                print_r($kw);
                print "</pre><hr>";
        }       

        $q = $sql_actions[$action]['sql']; 
        $q = strtr($q, $kw);

        if ($debug_level > 10) {
                print "<hr><pre>SQL query: $q </pre><hr>\n";
        }       

        $result = mysql_query($q, $dblink) or 
                user_error( "mysql query failed: \"$q\"\n" . mysql_error(),
                                E_USER_ERROR );

        $ans = array();

        if ($result) {
                $i = 0;
                while ( $ans[$i++] = mysql_fetch_assoc($result) )
                         ;

                unset($ans[$i-1]);
        }

        if ($debug_level > 20) {
                print "<hr><pre>SQL answer: \n";
                print_r($ans);
                print "<hr>\n";
        }       

        return $ans;
}


function sql_init() {
        global $db_host, $db_user, $db_pass, $db_name, $_SESSION;

        ($dblink = mysql_connect($db_host, $db_user, $db_pass)) or
                user_error( "mysql connection to host $db_host failed\n" .
                           mysql_error(), E_USER_ERROR );

        mysql_select_db($db_name, $dblink) or
                user_error( "database $db_name not present\n" . mysql_error(),
                            E_USER_ERROR );

        return $dblink;
}


function sql_exit($dblink) {
        global $_SESSION;
        mysql_close($dblink);
}

?>

---