Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?
- require_once("requestme.sql.php");
- //sample data
- $data = array(
- 'username' => 'my name' ,
- 'password' => '123456' ,
- 'email' => 'email@email.com' ,
- 'other' => 'other data'
- );
- $injection = <<< EOF
- " ' " ' " ' " ' " ' " ' " '''''' " ' OR something
- EOF;
- $sqls = <<< EOF
- INSERT INTO users (username,password) ('test' , 'test');
- INSERT INTO users (username,password) ('test1' , 'test1');
- INSERT INTO users (username,password) ('test2' , 'test2');
- INSERT INTO users (username,password) ('test3' , 'test3');
- INSERT INTO users (username,password) ('test4' , 'test4');
- INSERT INTO users (username,password) ('test5' , 'test5');
- UPDATE users SET something = `somewhere` WHERE `wack` = `capcap`;
- UPDATE users SET something = `somewhere` WHERE `wack` = `capcap`;
- EOF;
- $sql_file = "test.sql";
- ?>
- <html>
- <head>
- <style>
- PRE {
- border: 1px outset;
- padding-top: 10px;
- padding-bottom: 10px;
- padding-left: 5px;
- }
- BODY
- {
- font-family: tahoma;
- font-size: 13px;
- }
- </style>
- </head>
- <body>
- <b>SQL Generator</b> Hooray!<br>
- Here is a sample data<br>
- <pre>
- <?print_r($data);?>
- </pre><br>
- You can use <b>SQL::insert</b> to generate an <b>INSERT</b> query
- <pre>
- <?= SQL::insert("users" , $data) ?>
- </pre>
- Using <b>SQL::update</b> to generate a <b>UPDATE</b> query with or without condition
- <pre>
- <?= SQL::update("users" , $data , " user_id = " . SQL::quote('my_id')) ?><br>
- <?= SQL::update("users" , $data ) ?>
- </pre>
- and you can also generate <b>REPLACE</b> query with this class using <b>SQL::replace</b> (with UPDATE or INSERT syntax )
- <pre>
- <?= SQL::replace("users" , $data) ?><br>
- <?= SQL::replace("users" , $data , false) ?>
- </pre>
- even <b>DELETE</b> with <b>SQL::delete</b>
- <pre>
- <?=SQL::delete('users' , 'user_id = ' . SQL::quote('my_id'))?>
- </pre>
- Now you can create SQL Time or Datetime easier by using <b>SQL::time</b> and PHP Unix time
- <pre>
- <?
- echo SQL::time(time() , 'DATE') . "<BR>";
- echo SQL::time(time() , 'TIME') . "<BR>";
- echo SQL::time(time() , 'DATETIME') . "<BR>";
- ?>
- </pre>
- no more SQL injection , everything will be ok ^___^ (<b>SQL::quote</b>)
- <pre>
- <?=$injection?>
- <b>to</b>
- <?=SQL::quote($injection)?>
- </pre>
- render a simple equal condition with <b>SQL::condition</b>
- <pre>
- <?=SQL::condition(array('my_column' => 'my_data' , 'my_column2' => 'my_data', 'my_column3' => 'data 3'))?><br>
- <?=SQL::condition(array('my_column' => 'my_data' , 'my_column2' => 'my_data') , 'OR')?>
- </pre>
- or simple <b>IN</b> syntax <b>SQL::in</b>
- <pre>
- <?=SQL::in("my_column" , array('var1' , 'var2' , 'var3'))?>
- </pre>
- and finally , you can split queries to invidual query O__O
- <pre>
- <?=$sqls?>
- to
- <?print_r(SQL::split($sqls,false))?>
- </pre>
- and from sql file :) just using <b>SQL::split</b>
- <pre>
- <?print_r(SQL::split($sql_file))?>
- </pre>
- </body>
- </html>
- ------------------------------------------------------------
- <?
- class SQL
- {
- /**
- * Remove unnecessary string in a sql query
- * @param string $s String 1
- * @param string $s2 String need to be removed
- * @return string
- * @access private
- **/
- function trim($s,$s2) {
- if (substr($s , strlen($s) - strlen($s2)) == $s2) $s = substr($s , 0 , strlen($s) - strlen($s2));
- return $s;
- }
- /**
- * Quote a SQL value
- * @param string $s String need to be quoted
- * @return string
- **/
- function quote($s) {
- return "'".str_replace('\\"', '"', addslashes($s))."'";
- }
- /**
- * Generate SQL Insert Query
- * @param string $table Target table name
- * @param array $data SQL Data (ColumnName => ColumnValue)
- * @return string
- **/
- function insert($table,$data) {
- if (is_string($data)) {
- return "INSERT INTO $table $data;";
- }
- $field = '';
- $col = '';
- foreach ($data as $k => $v) {
- $field .= "`" . $k . "`,";
- $col .= SQL::quote($v) . ",";
- }
- $field = SQL::trim($field , ',');$col = SQL::trim($col , ',');
- return "INSERT INTO $table ($field) VALUES ($col);";
- }
- /**
- * Generate SQL Update Query
- * @param string $table Target table name
- * @param array $data SQL Data (ColumnName => ColumnValue)
- * @param string $cond SQL Condition
- * @return string
- **/
- function update($table,$data,$cond='')
- {
- $sql = "UPDATE $table SET ";
- if (is_string($data)) {
- $sql .= $data;
- } else {
- foreach ($data as $k => $v) {
- $sql .= "`" . $k . "`" . " = " . SQL::quote($v) . ",";
- }
- $sql = SQL::trim($sql , ',');
- }
- if ($cond != '') $sql .= " WHERE $cond";
- $sql .= ";";
- return $sql;
- }
- /**
- * Generate SQL Delete Query
- * @param string $table Target table name
- * @param string $cond SQL Condition
- * @return string
- **/
- function delete($table,$cond='')
- {
- $sql = "DELETE FROM $table";
- if ($cond != '') $sql .= " WHERE $cond";
- $sql .= ";";
- return $sql;
- }
- /**
- * Generate SQL replace query
- * @param string $table Target table name
- * @param array $data SQL Data (ColumnName => ColumnValue)
- * @param bool $update_sytac Use SET sytac or VALUES ()
- * @return string
- **/
- function replace($table , $data , $update_sytac = true) {
- $sql = "REPLACE $table ";
- if (is_string($data)) {
- $sql .= $data . ";";
- return $sql;
- }
- if ($update_sytac) {
- $sql .= "SET ";
- foreach ($data as $k => $v) {
- $sql .= "`" . $k . "`" . " = " . SQL::quote($v) . ",";
- }
- $sql = SQL::trim($sql , ',');
- $sql .= ";";
- } else {
- $field = '';
- $col = '';
- foreach ($data as $k => $v) {
- $field .= "`" . $k . "`" . ",";
- $col .= SQL::quote($v) . ",";
- }
- $field = SQL::trim($field , ',');$col = SQL::trim($col , ',');
- $sql .="($field) VALUES ($col);";
- }
- return $sql;
- }
- /**
- * Return SQL Time
- * @return string
- **/
- function time($value,$format="DATE") {
- $f = '';
- switch (strtoupper($format)) {
- case 'DATE':
- $f = 'Y-m-d';
- break;
- case 'TIME':
- $f = 'H:i:s';
- break;
- case 'DATETIME':
- default:
- $f = 'Y-m-d H:i:s';
- break;
- }
- return date($f , $value);
- }
- /**
- * Render simple equal condition
- *
- */
- function condition($conditions,$compare='AND')
- {
- foreach ($conditions as $key => $value)
- {
- $conditions[$key] = "`$key` = " . SQL::quote($value);
- }
- $sql = implode(" $compare " , $conditions);
- return $sql;
- }
- /**
- * Render simple in syntax
- *
- * @param unknown_type $value
- */
- function in($column,$values)
- {
- $sql = " `$column` IN ";
- if (!is_array($values)) $values = array($values);
- foreach ($values as $key => $value)
- {
- $values[$key] = sql::quote($value);
- }
- return $sql . "(" . implode("," , $values) . ")";
- }
- /**
- *
- *
- * Removes comment and splits large sql files into individual queries
- *
- *
- *
- * @param string the sql commands
- * @param bool Fetch SQL from file
- * @return array sqls
- */
- function split($sql,$file=true) {
- $ret = array();
- if ($file) $sql = implode('' , file($sql));
- $sql = trim($sql);
- $sql_len = strlen($sql);
- $char = '';
- $string_start = '';
- $in_string = false;
- for ($i = 0; $i < $sql_len; ++$i) {
- $char = $sql[$i];
- // We are in a string, check for not escaped end of
- // strings except for backquotes that can't be escaped
- if ($in_string) {
- for (;;) {
- $i = strpos($sql, $string_start, $i);
- // No end of string found -> add the current
- // substring to the returned array
- if (!$i) {
- $ret[] = $sql;
- return $ret;
- }
- // Backquotes or no backslashes before
- // quotes: it's indeed the end of the
- // string -> exit the loop
- else if ($string_start == '`' || $sql[$i-1] != '\\') {
- $string_start = '';
- $in_string = false;
- break;
- }
- // one or more Backslashes before the presumed
- // end of string...
- else {
- // first checks for escaped backslashes
- $j = 2;
- $escaped_backslash = false;
- while ($i-$j > 0 && $sql[$i-$j] == '\\') {
- $escaped_backslash = !$escaped_backslash;
- $j++;
- }
- // ... if escaped backslashes: it's really the
- // end of the string -> exit the loop
- if ($escaped_backslash) {
- $string_start = '';
- $in_string = false;
- break;
- }
- // ... else loop
- else {
- $i++;
- }
- } // end if...elseif...else
- } // end for
- } // end if (in string)
- // We are not in a string, first check for delimiter...
- else if ($char == ';') {
- // if delimiter found, add the parsed part to the returned array
- $ret[] = substr($sql, 0, $i);
- $sql = ltrim(substr($sql, min($i + 1, $sql_len)));
- $sql_len = strlen($sql);
- if ($sql_len) {
- $i = -1;
- } else {
- // The submited statement(s) end(s) here
- return $ret;
- }
- } // end else if (is delimiter)
- // ... then check for start of a string,...
- else if (($char == '"') || ($char == '\'') || ($char == '`')) {
- $in_string = true;
- $string_start = $char;
- } // end else if (is start of string)
- // for start of a comment (and remove this comment if found)...
- else if ($char == '#' || ($char == ' ' && $i > 1 && $sql[$i-2] . $sql[$i-1] == '--')) {
- // starting position of the comment depends on the comment type
- $start_of_comment = (($sql[$i] == '#') ? $i : $i-2);
- // if no "\n" exits in the remaining string, checks for "\r"
- // (Mac eol style)
- $end_of_comment = (strpos(' ' . $sql, "\012", $i+2)) ? strpos(' ' . $sql, "\012", $i+2) : strpos(' ' . $sql, "\015", $i+2);
- if (!$end_of_comment) {
- // no eol found after '#', add the parsed part to the returned
- // array and exit
- // RMV fix for comments at end of file
- $last = trim(substr($sql, 0, $i-1));
- if (!empty($last)) {
- $ret[] = $last;
- }
- return $ret;
- } else {
- $sql = substr($sql, 0, $start_of_comment) . ltrim(substr($sql, $end_of_comment));
- $sql_len = strlen($sql);
- $i--;
- } // end if...else
- } // end else if (is comment)
- } // end for
- // add any rest to the returned array
- if (!empty($sql) && trim($sql) != '') {
- $ret[] = $sql;
- }
- return $ret;
- }
- }
- ?>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement