Location: PHPKode > scripts > DbProxy > dbproxy/assets/DbProxy.html
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>DbProxy</title>
</head>
<body>
<h1>DbProxy</h1>
<h2>Description</h2>
<p>Takes an XML file, parses it and dynamically creates specific wrapper functions (aka <em>stubs</em>) that connect to a database to read/write data.</p>
<p> <em>Note: currently, the only supported database is MySQL. There are currently no plans to support a different database type.</em></p>
<h2>Features list</h2>
<p> <em>Note: The module is built on top of the </em>ParamsProxy<em> module, therefore it inherits its security model, base functionality and limitations.</em></p>
<ul>
  <li>
    <p>Provides a way to effectively separate program logic from data exchange logic.</p>
  </li>
  <li>
    <p><strong>Built-in protection against SQL injections: </strong>every char of user provided information is first encoded*, and only then sent to database, effectively making futile any attempt of naïve SQL injection.</p>
  </li>
</ul>
<p>* this implies that the module is required for every single operation that is to be made on the database. This limitation is assumed. </p>
<h2><em><strong>How it works</strong></em></h2>
<p>The module uses the PHP5 overloading feature to create specific <em>stubs</em> on the fly based on settings in the provided XML file. </p>
<p>The following section describes in detail the inner operation of the module.</p>
<ol>
  <li>
    <p>The class that uses module's abilities (from this point onward called <em>client class</em>) calls the relevant API method configure() on the unique instance of the module. Ex:</p>
    <pre>
 $dbProxy = new DbProxy();
 $dbProxy-&gt;configure();
</pre>
    <p><em>Note: The module must be called from within the execution context of another class. It will not work if called from the global scope.</em> </p>
  </li>
  <li>
    <p>This will attempt to locate and parse a corresponding XML database configuration file bound to the <em>client class</em>. Upon success, <em>stubs </em>data will be saved, based on the XML file's content.<br />
      <br />
      The basics on configuration files and their location are covered in the ParamsProxy.html document.<br />
      <br />
      The name of the data base configuration file is dbAccess.xml and is to be found at &lt;<strong>configuration files folder</strong>&gt;/&lt;<em>client class</em> <strong>name</strong>&gt;/dbAccess.xml.</p>
  </li>
  <li>
    <p>The <em>client class </em>issues a call to a <em>stub </em>name. Ex:</p>
    <pre>
 $dbProxy = new DbProxy();
 $dbProxy-&gt;getEmployees (“employees”, “sex”, “M”);</pre>
    <p>The module's class catches the call to a non-existing method in its magic “__call()” function and attempts to locate/match a corresponding <em>stub</em> for the client class. <br />
      Note: the module uses the PHP <em>debug trace </em>feature to determine the <em>client class</em> name.</p>
  </li>
  <li>
    <p>If the match succeeds, the specific <em>stub</em> is run with arguments provided by the <em>client class</em>, and results of the database query are interpreted and passed back.<br />
      <br />
      <em>Note: “interpretation” of data base results is controlled on a per-stub basis, in the XML database configuration file.</em></p>
  </li>
</ol>
<p>Any failures in the above scenario, including SQL syntax errors will produce fatal errors, thus forcing the developer to finely tune SQL query templates and/or provided arguments.</p>
<h2><em>XML</em> <em>format</em></h2>
<p>There is no custom XML format imposed by the module. The module expects data in the default configuration file format, as defined by the ParamsProxy.html document. </p>
<p>The general form of a database configuration file follows (bold values must be given literally):</p>
<pre>
&lt;config for=&quot;MyClass&quot;&gt;
	&lt;!-- Connection data for class MyClass --&gt;
	&lt;param&gt;
		&lt;name&gt;<strong>connection</strong>&lt;/name&gt;
		&lt;type&gt;<strong>array</strong>&lt;/type&gt;
		&lt;value&gt;
			&lt;item key=&quot;<strong>dbName</strong>&quot; type=&quot;string&quot;&gt;MyDatabaseName&lt;/item&gt;
			&lt;item key=&quot;<strong>dbHost</strong>&quot; type=&quot;string&quot;&gt;127.0.0.1&lt;/item&gt;
			&lt;item key=&quot;<strong>dbUser</strong>&quot; type=&quot;string&quot;&gt;JonDoe&lt;/item&gt;
			&lt;item key=&quot;<strong>dbPassword</strong>&quot; type=&quot;string&quot;&gt;1234&lt;/item&gt;
		&lt;/value&gt;
	&lt;/param&gt;

	&lt;!-- Sample stub definition. --&gt;
	&lt;param&gt;
		&lt;name&gt;getEmployees&lt;/name&gt;
		&lt;type&gt;<strong>array</strong>&lt;/type&gt;
		&lt;value&gt;
			&lt;item key=&quot;<strong>queryParams</strong>&quot;&gt;
				MY_TABLE, MY_COLUMN, MY_VALUE
			&lt;/item&gt;
			&lt;item key=&quot;<strong>queryTemplate</strong>&quot;&gt;
				&lt;![CDATA[
					SELECT * FROM %MY_TABLE% WHERE `%COLUMN%` = &quot;%MY_VALUE%&quot;;
				]]&gt;
			&lt;/item&gt;
			&lt;item key=&quot;<strong>queryResultType</strong>&quot; type=&quot;<strong>string</strong>&quot;&gt;array&lt;/item&gt;
		&lt;/value&gt;
	&lt;/param&gt;
&lt;/config&gt;</pre>
<p>Although the module performs no syntax validation (as it is handled by the underlying ParamsProxy module, it will actively validate form and content-wise data passed in, and produce fatal errors in case of failure.<br />
  <br />
  Following items are subject of validation:</p>
<ul>
  <li>
    <p>parameters and function names must match <code>/^[_a-zA-Z]+[_a-zA-Z0-9]*$/</code>;</p>
  </li>
  <li>
    <p>no parameter or function may be named <code>“connection”</code>, regardless of case;</p>
  </li>
  <li>
    <p>there must be one and only one <code>“connection”</code> parameter;</p>
  </li>
  <li>
    <p>there cannot be two parameters with the same name;</p>
  </li>
  <li>
    <p>within the <code>“connection”</code>:</p>
    <ul>
      <li>
        <p>all items, with their given keys are required and mustn't be null or the empty string;</p>
      </li>
      <li>
        <p>a formally valid host name or IP address is required; <em>localhost</em> is tolerated;</p>
      </li>
      <li>
        <p>only the main Latin letters subset plus underscore is allowed for user name; the <strong>root</strong> user name is also forbidden;</p>
      </li>
      <li>
        <p>only the printable chars of the main ASCII set are allowed for user password, with a few notable exceptions: the <strong>backslash</strong>, <strong>apostrophe</strong>, <strong>double quotes</strong>, <strong>dash</strong>, and <strong>back tick</strong> chars.</p>
      </li>
      <li>the empty password is also forbidd</li>
    </ul>
  </li>
</ul>
<p>Note: this implies that the module cannot be used on machines that only provide root access to the MySQL server, or on some publicly accessible MySQL servers. This limitation is assumed.</p>
<p>The module itself requires no configuration.</p>
<h2>MySQL Query Results Interpretation</h2>
<p>The actual value(s) returned by the module to the calling function heavily depend on the <strong>queryResultType </strong>setting of a specific stub. Except for the “array” setting, there is a bit of <em>heuristic</em> involved to obtain a response of the expected type. The algorithm is outlined below:</p>
<ol>
  <li>
    <p>Is the result <strong>FALSE</strong> and the last <strong>MySQL</strong> error was not an empty string? Re-throw the MySQL exception in PHP and exit.</p>
  </li>
  <li>
    <p>Our caller is expecting a <strong>boolean</strong>; is the result <strong>FALSE</strong>? Return <strong>false</strong>. Is the result <strong>TRUE</strong> and we have had either <strong>affectedRows</strong> or <strong>numRows</strong> greater than 0? Return <strong>true</strong>. None of the above? Return <strong>false</strong>.</p>
  </li>
  <li>
    <p>Our caller is expecting a <strong>string</strong>; is the result comprised of a <strong>single row</strong> having a <strong>single cell</strong>? Return it as a string. Is the result comprised of <strong>several rows</strong>, having <strong>one or several cells</strong> each? Concatenate all as a HTTP <strong>GET</strong> string and return it. None of the above? Return the <strong>empty string</strong>.</p>
  </li>
  <li>
    <p>Our caller is expecting a number; is the result comprised of a <strong>single row</strong> having a <strong>single cell</strong>? Is that value <strong>numeric</strong>? Cast it to an <strong>int</strong> or <strong>float</strong> and return it. Or, get the largest value of either <strong>affectedRows</strong> or <strong>numRows</strong> and return it<strong>.</strong></p>
  </li>
  <li>
    <p>Our caller is expecting an array; there is nothing to assume/guess. Return the result as a multidimensional array.</p>
  </li>
</ol>
<p> <em>Note: the module will only return number-indexed array (not associative, nor double-indexed). The rationale behind that is that the client code must have a good idea on the database structure, and returning the column names would be futile.</em></p>
<h2>Known Limitations, Gotchas</h2>
<ul>
  <li>
    <p>The <strong>queryResultType</strong> must be given as a string:<br />
      <br />
      Ex: <code>&lt;item key=&quot;queryResultType&quot; type=&quot;string&quot;&gt;null&lt;/item&gt;</code><br />
      <br />
      will set the <strong>queryResultType</strong> to &quot;null&quot;, while:<br />
      <br />
      Ex: <code>&lt;item key=&quot;queryResultType&quot;&gt;null&lt;/item&gt;</code><br />
      <br />
      will set it to &quot;&quot; (empty string).</p>
  </li>
  <li>
    <p>The <strong>queryResultType </strong>can only be “string”, “boolean”, “number”, “array” and “null”. Unlike ParamsProxy, the module does not provide an “auto” type. If left unspecified – i.e., the entire <code>&lt;item key=&quot;queryResultType&quot;...&gt;</code> node is missing – “array” is assumed. </p>
  </li>
  <li>
    <p>You must escape <em>all</em> values, even if they're not reserved words:<br />
      <strong>Wrong</strong>: <code>SELECT * FROM %MY_TABLE% WHERE %MY_COLUMN% = %MY_VALUE%;</code><br />
      <strong>Right</strong>: <code>SELECT * FROM `%MY_TABLE%` WHERE `%MY_COLUMN%` = <strong>&quot;</strong>%MY_VALUE%<strong>&quot;</strong>.</code></p>
  </li>
  <li>
    <p>Don't place XML comments within the <strong>queryTemplate</strong> item node. Due to a limitation in the underlying ParamsProxy module, they will become part of the template (and usually cause a fatal error).</p>
  </li>
  <li>
    <p>If a stub is sent an <strong>integer</strong> or <strong>float</strong> as a parameter, it will be passed to MySQL <strong>unencoded</strong>, so that arithmetic operations in queries can be achieved. Send the parameter as <strong>string</strong> if you intend to match it literally, within a larger string.</p>
  </li>
  <li>
    <p>Despite the fact that PHP returns <strong>strings</strong> for all <em>MySQL types</em> except the <strong>NULL</strong> type, the module will actually return <strong>integer</strong> or <strong>floats</strong> for <em>MySQL numeric types</em> (provided they fit in the <strong>int</strong> or <strong>float</strong> ranges on the current host – otherwise, strings will be returned).</p>
  </li>
  <li>
    <p>The module expects to be given UTF-8 strings.</p>
  </li>
  <li>
    <p>Due to encoding, identifiers length has been truncated to half (32 chars instead of 64 for table names, etc.)</p>
  </li>
</ul>
</body>
</html>
Return current item: DbProxy