sql.php File Reference

SQL database functions. More...

Go to the source code of this file.

Functions
	sql_quote ($value, $dblink)
	quote a value
	sql_kw_parse ($action, $kw, $dblink)
	parse keywords
	sql_query ($action, $kw, $dblink)
	perform an SQL "action"
	sql_init ()
	initalize a SQL database connection
	sql_exit ($dblink)
	close a SQL database connection
Variables
	$sql_actions
	SQL actions.

---

Detailed Description

SQL database functions.

Definition in file sql.php.

---

Function Documentation

sql_exit

(

$

dblink

)

close a SQL database connection

Parameters:

$dblink

an open database handle

Definition at line 329 of file sql.php.

Referenced by do_action(), do_login(), setpw(), show_collection(), show_index(), and show_users().

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

sql_init

(

)

initalize a SQL database connection

Returns:

an open database handle

Definition at line 311 of file sql.php.

Referenced by do_action(), do_login(), main(), show_collection(), show_index(), and show_users().

                    {
        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;
}

sql_kw_parse	(	$	action,
		$	kw,
		$	dblink
	)

parse keywords

Parameters:

	$action	-- an SQL action
	$kw	-- user-supplied arguments
	$dblink	-- SQL database link

Returns:

an associative array for use in strtr()

This is a helper function for sql_query() and returns an associative array that maps "SQL placeholders" (e.g. '@v:foo@', '@vq:foo@', etc.) to the appropriate substitiutions as determined from $kw (e.g. $kw['foo']). Quoting is performed where necessary.

The resulting array is suitable for use in a strtr() function. It can be used to substitute the placeholders in an SQL statement prototype with their real values, thus turning the SQL statement prototype into a real SQL statement.

Definition at line 121 of file sql.php.

References $sql_actions, and sql_quote().

Referenced by sql_query().

                                                {

        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;
}

Here is the call graph for this function:

sql_query	(	$	action,
		$	kw,
		$	dblink
	)

perform an SQL "action"

This function performs one of several well-defined "actions" on the database. The supported actions are defined in $sql_actions[].

Parameters:

	$action	-- the action to be performed
	$kw	-- an associative array containing user-supplied arguments for the action
	$dblink	-- an open database handle

Returns:

the result of the query

Each action is defined as an SQL statement prototype that may contain placeholders. It is possible for the caller to submit data to an action via the $kw parameter. First, the placeholders in the prototype are replaced by the data from $kw[]. Afterwards, the SQL statement is executed.

Note:

To prevent SQL injection attacks, a mechanism has been devised to ensure proper quotation of all arguments. Where so required, special placeholders can be used to enforce quotation of string-valued arguments (e.g. inside an INSERT statement). (Integer-valued arguments are never quoted, regardless of whether quotation is required.)

Recognized placeholders in SQL statements include:

• @v:foo@ -- value of $kw['foo'], unquoted
• @vq:foo@ -- value of $kw['foo'], quoted

Some special placeholders are supported if $kw['foo'] is itself an associative array:

• @k:foo@ -- list of keys in $kw['foo'], comma-separated, unquoted
• @v:foo@ -- -- list of values in $kw['foo'], comma-separated, unquoted
• @vq:foo@ -- list of values in $kw['foo'], comma-separated, quoted
• @kv:foo@ -- "key=value" pairs, comma-separated, value is unquoted
• @kvq:foo@ -- "key=value" pairs, comma-separated, value is quoted

Definition at line 252 of file sql.php.

References $sql_actions, and sql_kw_parse().

Referenced by coll_set_state(), do_delete_item(), do_login(), edit_item(), expire(), extend_loan(), get_html_options(), get_item_owner(), main(), may_delete_item(), report(), send_reminder(), set_item_state(), set_random_pw(), setpw(), show_collection(), show_index(), show_users(), view_email(), and view_item().

                                             {

        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;
}

Here is the call graph for this function:

sql_quote	(	$	value,
		$	dblink
	)

quote a value

Parameters:

	$value	-- unquoted value
	$dblink	-- open database handle

Returns:

quoted value, suitable for use in an SQL statement

Definition at line 73 of file sql.php.

Referenced by sql_kw_parse().

                                     {

   # 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;
}

---

Variable Documentation

$sql_actions

SQL actions.

This array defines SQL "actions" to be performed by sql_query().

Definition at line 16 of file sql.php.

Referenced by sql_kw_parse(), and sql_query().

---