This page lets you view source code from my server. The program uses a brute force code formatter to color code elements. NOTE: I wrote this program while trying to learn the vim text editor. This is not my usual coding style.
Use this select box to select a file.
This function produces a SQL statement from an array such as $_POST and a block of validation text.
Below is the code all formatted with bright colors. The program links to files opened with include() and expands those opened with require(). Clicking on the require line should change visibility.
sqlBlock
001 <!DOCTYPE>
002 <html>
003 <?php
004 /**
005 * This page tests the sqlBlock object. This object creates SQL from a
006 * block of text and an array such as $_POST or $_GET.
007 * The program executes the SQL with the sqlExec() program found in
008 * sql.php which referenced in rmHead.
009 * @see https://yintercept.com/resources/sqlTest.php
010 * This page will create and display a variety SQL statements.
011 */
012 include('/var/www/php/rmHead.php'); // links to 1
require('/var/www/php/sqlBlock.php');001 <?php
002 define('LOG_DIR','/var/www/log/');
003 /**
004 * @package ResourceModel
005 * sqlBlock will create an INSERT, UPDATE, REPLACE or DELETE SQL statement.
006 * You can define the columns and validation in a single block or add
007 * items one by one.
008 *
009 * @copyright 2007-2016 kd
010 * The test script is found here:
011 * @see https://yintercept.com/resources/blockTest.php
012 * The program executes the SQL with sqlExec() from the sql.php file.
013 * @see https://yintercept.com/resources/sqlTest.php
014 *
015 * Program by Kevin Delaney ( https://yintercept/resources/ )
016 * 2007-04-30 : kd. Alpha Release.
017 * 2007-05-04 : kd. Made log private, it is now called from execSQL.
018 * 2016-01-30 : kd. Updated for Resource Model
019 * 2016-08-23 : kd. changed order so that we define the columns then bindValues
020 */
021 class sqlBlock {
022 private $dbi; // reference to the database holding the Table.
023 private $table; // name of table to update
024 private $command; // a single character (i,r,u,d) denotes the command.
025 private $limit; // optional limit on rows updated.
026 private $colCnt=0; // number of columns in the query.
027 // private $colArr = array(); // Multidimensional Array defines columns and validation.
028 public $colArr = array(); // holds the data for the statement.
029 // translates the $command character into a SQL command.
030 private $actions = array('i'=>'INSERT INTO ','u'=>'UPDATE ', 'd'=>'DELETE FROM ','r'=>'REPLACE INTO ');
031 public $stripTags=true;
032 /**
033 * __construct defines the table and command for the SQL Statement.
034 * @PARAM string $cmd is 1st letter of 'I'NSERT, 'R'EPLACE, 'U'PDATE or 'D'ELETE
035 * NOTE: I like pass the whole word to make my program clear.
036 * @PARAM string $table is the name of the Table to modify.
037 * @PARAM string $dbi is the database containing the table.
038 */
039 function __construct ($cmd, $table, $dbi=DB_MAIN) {
040 $this->command = strtolower(substr($cmd,0,1));
041 if (!isset($this->actions[$this->command])) msgError('Invalid command '.$cmd);
042 $this->table = $table;
043 $this->dbi = $dbi;
044 $sqlStr = ''; // This is the main SQL Statement
045 $valStr = ''; // The VALUES section for INSERT and REPLACE.
046 $whereStr=''; // The WHERE string for an UPDATE Statement.
047 $this->colCnt=0;
048 $this->delimiter=' '; // changes to a comma after first value
049 $this->limit=null; // You can limit affected rows of an UPDATE statementi
050 }
051 /**
052 * defColumn() defines a column for query and validates the data.
053 * NOTE, you must add the data before defining the column!
054 * @PARAM string $colName is the name of the column in the Table.
055 * @PARAM mixed $required is a directive for the column.
056 * values can be true, false, "y", "n" or "w"
057 * "w" means the value is in the WHERE clause.
058 * @PARAM string $type is the data type of the column
059 * @PARAM mixed $w_or_len is either 'w' indicating that variable is part of
060 * the key in WHERE clause or maximum length of the column.
061 */
062 public function defColumn($colName, $req=false, $type='str', $max=0, $val=null) {
063 // the format of $colArr is
064 // [colName, required, type, max, dataSource, val]
065 // if value is null, set the datasource to p for $_POST.
066 $reqStr = '<b>'.$colName.'</b> is a required field';
067 if (is_string($req)) {
068 if ($req=='') {
069 $req=false;
070 } elseif (strlen($req) == 1) {
071 $req = in_array(substr($req,0,1),['y','Y','t','T','r','R']);
072 } else {
073 // if there is more than one spaces assume req is a whole sentence.
074 $reqStr = (substr_count($req,' ') < 2)? '<b>"'.$req.'"</b> is a required field.' : $req;
075 $req = true;
076 }
077 }
078 $dsrc = (is_null($val))? 'p': 'v';
079 if ($colName == 'where') {
080 // separates UPDATE SET and WHERE clause.
081 // not needed on Delete as everything is in WHERE.
082 $type = 'where';
083 } elseif ($type == 'now') {
084 $type = 'str';
085 $val = $GLOBALS['rmSite']->jd;
086 } elseif ($type=='today') {
087 $type = 'int';
088 $val = floor($GLOBALS['rmSite']->jd + .5);
089 } elseif ($type=='site') {
090 $type = 'int';
091 $val = $GLOBALS['rmSite']->id;
092 } elseif ($type=='user') {
093 $type = 'int';
094 $dsrc='v';
095 $val = $GLOBALS['rmUser']->id;
096 } elseif ($type=='ip') {
097 $type = 'ip';
098 $val = $GLOBALS['rmIP']->id;
099 } elseif ($type=='sess') {
100 $type = 'int';
101 $val = $GLOBALS['rmUser']->session;
102 } elseif ($dsrc=='p') {
103 // the data is found in $_POST.
104 if (isset($_POST[$colName])) {
105 if ($req && $_POST[$colName] == '') msgError($reqStr);
106 if ($type == 'int') {
107 // cast to int and put data in $val.
108 $val = (int) $_POST[$colName];
109 $dsrc = 'v';
110 if ($max > 0 && $val > $max) msgError('<b>'.$colName.'</b> is greater than maximum value '.$max);
111 } elseif ($type=='color') {
112 $type='int';
113
114 $val = hexdec($_POST[$colName]); // strip off the #
115 msgNote('Col is '.$_POST[$colName].' = '.$val);
116 } else {
117 if ($max > 0 && strlen($_POST[$colName]) > $max) msgError('The maximum length of <b>'.$colName.'</b> is '.$max.' characters.');
118 }
119 } else {
120 if ($req) msgError($reqStr);
121 $dsrc = 'v'; // change dataSource to 'v' so we don't check $_POST again.
122 // I prefer having 0 to null in integer fields.
123 $val = ($type=='int')? 0 : '';
124 }
125 } else {
126 if ($type == 'int') {
127 // cast the value to int.
128 $val = (int) $val;
129 if ($max > 0 && $val > $max) msgError('<b>'.$colName.'</b> is greater than maximum value '.$max);
130 } elseif ($type=='color') {
131 $type='int';
132 $val = hexdec($val); // strip off the #
133 } else {
134 if ($max > 0 && strlen($_POST[$colName]) > $max) msgError('The maximum length of <b>'.$colName.'</b> is '.$max.' characters.');
135 }
136 }
137 if ($val !== null) $dsrc = 'v';
138 if ($colName != '') $this->colArr[$this->colCnt++] = [$colName, $req, $type, $max, $dsrc, $val, ($type=='html')? false : $this->stripTags];
139 return $val;
140 }
141 public function addCol($colName, $val, $type='str', $req=false, $max=0) {
142 $this->defColumn($colName, $req, $type, $max, $val);
143 }
144 /**
145 * addColumn() is the same as defColumn, with parameters in a different order.
146 */
147 public function addColumn($colName, $val, $type='str', $max=0, $req=false) {
148 $this->defColumn($colName, $req, $type, $max, $val);
149 }
150 /**
151 * addBlock() will take a delimited string and add a block of columns.
152 * The program calls defColum() with each row in the block.
153 * @PARAM string $block is a formatted block defining multiple columns
154 * @PARAM string $delim is the data elimiter for the block.
155 * @PARAM string $lineDelim is the line delimiter for the block.
156 */
157 public function addBlock($block, $delim=',', $lineDelim = PHP_EOL) {
158 $tok = strtok($block, $lineDelim);
159 while ($tok !== false) {
160 list($colName, $required, $type, $max, $val) = explode($delim,rtrim($tok).$delim.$delim.$delim.$delim);
161 if ($val=='') $val = null;
162 $this->defColumn($colName, $required, $type, $max, $val);
163
164 $tok = strtok($lineDelim);
165 }
166 }
167 /**
168 * getSQL() produces the SQL with marked variables.
169 * @return program returns a SQL statement.
170 */
171 public function getSQL() {
172 $rv = '';
173 $delim = '';
174 $inSetDef= ($this->command == 'u')? true : false;
175 $valStr = ''; // used on INSERT and REPLACE
176
177 if ($this->command == 'd') {
178 $rv = ($this->whereStr != '')? '' : 'DELETE FROM '.$this->table.' WHERE ';
179 } elseif ($this->command == 'u') {
180 $rv = 'UPDATE '.$this->table.' SET ';
181 } elseif ($this->command == 'i') {
182 $rv = 'INSERT INTO '.$this->table.' (';
183 $valStr = ') VALUES (';
184 } elseif ($this->command == 'r') {
185 $rv = 'REPLACE INTO '.$this->table.' (';
186 $valStr = ') VALUES (';
187 } else {
188 msgError('Invalid Command '.$this->command);
189 }
190 // it is cleaner to process INSERT and UPDATE in different loops.
191 if ($this->command == 'i' or $this->command == 'r') {
192 for ($i=0; $i<$this->colCnt; $i++) {
193 $rv .= $delim.$this->colArr[$i][0];
194 $valStr .= $delim.':'.$this->colArr[$i][0];
195 if ($delim == '') $delim = ', ';
196 }
197 $valStr .= ')';
198 } elseif ($this->command == 'u' or $this->command == 'd') {
199 $delim = '';
200 for ($i=0; $i<$this->colCnt; $i++) {
201 if ($this->colArr[$i][0] == 'where') {
202 $inSetDef = false;
203 $delim = '';
204 $rv .= ' WHERE ';
205 } else {
206 $rv .= $delim.$this->colArr[$i][0].' = '.':'.$this->colArr[$i][0];
207 if ($delim == '') {
208 $delim = ($inSetDef)? ', ' : ' and ';
209 }
210 }
211 }
212 // There was no where clause in this statement.
213 // to avoid whole table operations set $sql to '';
214 if ($delim != ' and ') {
215 msgError('No Where Clause in SQL: <pre>'.$rv.'</pre>');
216 }
217 }
218
219 return $rv.$valStr;
220 }
221 /**
222 * exec() calls sqlExec with built statement to execute the SQL
223 * @param string $successMsg is displayed on success.
224 * @param string $errMsg is displayed on error.
225 * @param string $logFile will hold error messages for admin.
226 */
227 public function exec($successMsg='',$errMsg='',$logFile='') {
228 $rv = -1;
229 if (msgOkay()) {
230 try {
231 $sql = $this->getSQL();
232 $st = dbConn($this->dbi,$sql);
233 if ($st === false) {
234 msgError('PDO Prepare Failed');
235 msgError(dbConn($this->dbi,DB_ERRORS));
236 } else {
237 // bind values
238 for ($i=0; $i<$this->colCnt; $i++) {
239 if ($this->colArr[$i][4] == 'v') {
240 $pdoType = ($this->colArr[$i][2] == 'int')? PDO::PARAM_INT : PDO::PARAM_STR;
241 $st->bindValue($this->colArr[$i][0],$this->colArr[$i][5],$pdoType);
242 } elseif ($this->colArr[$i][0] == 'where') {
243 // skip
244 } elseif ($this->colArr[$i][4] == 'p') {
245 if ($this->colArr[$i][6]) {
246 $st->bindValue($this->colArr[$i][0],strip_tags($_POST[$this->colArr[$i][0]]));
247 } else {
248 $st->bindValue($this->colArr[$i][0],$_POST[$this->colArr[$i][0]]);
249 }
250 } // skip this row.
251 }
252 // msgNote('Executing SQL');
253 $stx = $st->execute();
254 if ($stx===false) {
255 msgError('PDO Statement Failed.');
256 msgComment(implode('<br />',$st->errorInfo()));
257 msgComment('SQL: '.$sql);
258 // $st->debugDumpParams();
259 } else {
260 $exId = dbConn($this->dbi,DB_INSERT_ID);
261 $exCnt = $st->rowCount();
262 if ($successMsg != '') msgNote(str_replace(['%ID','%CNT'],[$exId,$exCnt],$successMsg));
263 $rv = ($this->command == 'i')? $exId : $exCnt;
264 }
265 }
266 } catch (PDOException $e) {
267 msgError('PDO Error '.$e->getMessage());
268 }
269 }
270 // if (strtolower($log) != '') $this->log($logFile);
271 return $rv;
272 }
273 }
274 ?>
// End Require013
014 ?>
015 <head>
016 <meta charset="UTF-8" />
017 <meta name="viewport" content="width=device-width, initial-scale=1" />
018 <meta name="Author" content="Kevin Delaney" />
019 <meta name="keywords" content="dynamic sql in php" />
020 <meta name="description" content="sqlBlock builds a SQL statement and validates data from the $_POST array." />
021 <title>Test Page for sqlBlock</title>
022 <link rel="canonical" href="https://yintercept.com/resources/sqlBlock.php" />
023 <link rel="stylesheet" href="rm.css" type="text/css">
024 </head>
025 <h1 id="pageTitle"><a href="https://yintercept.com/resources/">Resource Model</a></h1>
026 <!-- The most critical part of any web page is the Ad!, without the ad we perish -->
027 <div class="ad">
028 <script async src="//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script>
029 <!-- yintercept - responsive goodle ad -->
030 <ins class="adsbygoogle"
031 style="display:block"
032 data-ad-client="ca-pub-7057064824800338"
033 data-ad-slot="4504818749"
034 data-ad-format="auto"></ins>
035 <script>
036 (adsbygoogle = window.adsbygoogle || []).push({});
037 </script>
038 </div>
039 <body>
040 <div class="main">
041 <h2>sqlBlock Test Page</h2>
042 <p>This page shows tests of the sqlBlock Object (<a href="https://yintercept.com/resources/view.php?script=14">View Source</a>). NOTE, this public test prints out the SQL produced from the statement. I decided not to put the page that tests the execution of this SQL on this page.</p>
043 <?php
044 /**
045 * Let's test some of the features of sqlBlock. We will create some test data.
046 * These tests only produce statements.
047 */
048 $testArr = ['one'=>1, 'two'=>2,'three'=>'Third Item','four'=>'Fourth Item'];
049 echo '<h3>Test INSERT</h3>';
050 echo '<p>This is the test of an INSERT statement:</p>';
051 // Step One: Define statement type and Table in constructor
052 $sb = new sqlBlock('i','Test_Table');
053 // Add the Data:
054 $sb->addArray($testArr);
055 // Define the columns
056 $sb->addBlock('one,int,y,10
057 two,int,y,10
058 three,str,y,24
059 four,str,y,24');
060 // Get the Resulits:
061 echo '<p>'.$sb->getSQLWithData().'</p>';
062 $sb=null;
063 msgHTML(); // show any errors
064 // Let's Try Same Thing with REPLACE
065 echo '<h3>Test REPLACE</h3>';
066 echo '<p>This is the test of a REPLACE statement:</p>';
067 // Step One: Define statement type and Table in constructor
068 // Note, I decided to spell out the who word. It should still work.
069 $sb = new sqlBlock('REPLACE','Test_Table');
070 // Add the Data:
071 $sb->addArray($testArr);
072 // Define the columns
073 $sb->addBlock('one,int,y,10
074 two,int,y,10
075 three,str,y,24
076 four,str,y,24');
077 // Get the Resulits:
078 echo '<p>'.$sb->getSQLWithData().'</p>';
079 $sb=null;
080 msgHTML();
081 // Let's Try Same Thing with UPDATE
082 echo '<h3>Test UPDATE</h3>';
083 echo '<p>This is the test of a UPDATE statement:</p>';
084 // Step One: Define statement type and Table in constructor
085 // Note, I decided to spell out the who word. It should still work.
086 $sb = new sqlBlock('u','Test_Table');
087 // Add the Data:
088 $sb->addArray($testArr);
089 // Define the columns
090 // NOTE, I changed the block to indicate the first columns are part of the
091 //where clause.
092 $sb->addBlock('one,int,y,w
093 two,int,y,w
094 three,str,y,24
095 four,str,y,24');
096 // Let's add a fifth column for old times sake.
097 $sb->addColumn('five','Fifth Column','str',true);
098
099 // Get the Resulits:
100 echo '<p>Statement: '.$sb->getSQLWithData().'</p>';
101 $sb = null;
102 msgHTML();
103 // Let's Delete some data and trigger an error.
104 echo '<h3>Test Required</h3>';
105 echo '<p>This is the test of the required constraint. The test creates the SQL with a missing piece of data. There should be an error preventing its execution.</p>';
106 // Step One: Define statement type and Table in constructor
107 // Note, I decided to spell out the who word. It should still work.
108 $sb = new sqlBlock('u','Test_Table');
109 $testArr['two'] = null; // delete some data
110 // Add the Data:
111 $sb->addArray($testArr);
112 // Define the columns
113 // NOTE, I changed the block to indicate the first columns are part of the
114 //where clause.
115 $sb->addBlock('one,int,y,w
116 two,int,y,w
117 three,str,y,24
118 four,str,y,24');
119
120 // Get the Resulits:
121 echo '<p>Statement: '.$sb->getSQLWithData().'</p>';
122 msgHTML(); // This should print an error message.
123 echo '<pre>
124 This var_dump() shows the data from the object
125 ';
126 var_dump($sb);
127 echo '</pre>';
128 msgHTML();
129 ?>
130 </div>
131 </html>
132 </body>
Use "view source" from your browser to grab the output. Feel free to link to this project and check out the Resource Model for information on PHP coding or my tumblr blog for picture of Arizona, Colorado or Utah.
File last modified at January 20 2020 07:55:31.. This page has been viewed 3838 Times.
blog ~ Resource Model ~ links