API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jan 31st, 2017
95
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 15.86 KB | None | 0 0
raw download clone embed print report
  1. insertQuery($table, $values)
  2. updateQuery($table, $values)
  3. isSqlSafe($str)
  4. sanitizeForQuery($str, $blacklist = NULL, $whitelist = NULL)
  5. escapeSpecialChars($str)
  6.  
  7. public function setData($query)
  8. public function getData($query, $returnType = self::SINGLE_ROW)
  9. public function get_table_names($filter = NULL)
  10. function get_last_arraysize()
  11. public function get_affected_rows()
  12. public function get_last_insert_id()
  13. public function get_last_query()
  14. public function get_error_number()
  15. public function get_error()
  16. public function get_database_name()
  17. public function get_username()
  18. public function change_database($new_database, $username = NULL, $password = NULL)
  19. public function terminate()
  20. public static function insertQuery($table, $values)
  21. public static function updateQuery($table, $values)
  22. public static function isSqlSafe($str)
  23. public static function sanitizeForQuery($str, $blacklist = NULL, $whitelist = NULL)
  24. public static function escapeSpecialChars($str)
  25. private function retry_deadlock($query)
  26.  
  27. $sql = new SqlObject();
  28.  
  29. //Reuse the $sql object for both functions
  30. getNames($sql);
  31. getOccupation('John Doe', $sql);
  32.  
  33. function getNames($sql = NULL){
  34. if($sql == NULL) { $sql = new SqlObject(); }
  35.  
  36. $query = "SELECT `ID`,`name` FROM `table` WHERE 1";
  37. $result = $sql->getData($query, SqlObject::MULTI_ROW);
  38.  
  39. $numRows = $sql->get_last_arraysize();
  40. for($i = 0; $i < $numRows; $i++){
  41. printf("Name (ID=%d): %sn", $result[$i]['ID'], $result[$i]['name']);
  42. }
  43. }
  44.  
  45. function getOccupation($name, $sql = NULL){
  46. if($sql == NULL) { $sql = new SqlObject(); }
  47.  
  48. if(!SqlObject::is_sql_safe($name)){
  49. throw new Exception("Unsafe user input");
  50. return FALSE;
  51. }
  52.  
  53. $query = "SELECT `occupation` FROM `jobs` WHERE `name` LIKE '$name'";
  54. $result = $sql->getData($query, SqlObject::SINGLE_ROW); //Single row (assoc)
  55.  
  56. if($result !== NULL){
  57. printf("Name: %s Occupation: %sn", $result['occupation']);
  58. }
  59. }
  60.  
  61. <?php
  62. /*
  63. SqlObject.php - A SQL php helper module.
  64. Copyright (c) Jacob Psimos
  65. All rights reserved.
  66.  
  67. Redistribution and use in source and binary forms, with or without
  68. modification, are permitted provided that the following conditions are
  69. met:
  70.  
  71. * The original author's name (Jacob A Psimos) remain in this file or any
  72. refactored versions.
  73.  
  74. * The original author's name (Jacob A Psimos) be included in the acknowledgements
  75. of the application using this library.
  76.  
  77. THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
  78. "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
  79. LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
  80. A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
  81. OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
  82. SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
  83. LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
  84. DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
  85. THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  86. (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
  87. OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  88. */
  89.  
  90.  
  91. class SqlObject{
  92.  
  93.  
  94. /* CONFIGURABLE VARIABLES - hard code these or use object contructor every time */
  95. private $SERVER_ADDRESS = 'localhost';
  96. private $SERVER_PORT = 3306;
  97. private $DATABASE_NAME = '';
  98. private $USERNAME = '';
  99. private $PASSWORD = '';
  100. private $MYSQLI_CHARSET = 'utf8'; //change for a custom charset
  101.  
  102. /* PRIVATE GLOBAL VARIABLES */
  103. private $connection = NULL;
  104. private $last_count = 0;
  105. private $last_query = '';
  106. const SINGLE_ROW = 0;
  107. const MULTI_ROW = 1;
  108. const ERR_DUPLICATE = 1062;
  109. const ERR_DEADLOCK = 1213;
  110. const RETRY_DEADLOCK_ATTEMPTS = 3;
  111. const ACCESS_DENIED_CHANGE_USER = 1873;
  112. const ACCESS_DENIED_DATABASE = 1044;
  113. const ACCESS_DENIED_USER = 1045;
  114.  
  115.  
  116. /*
  117. If no username and no password and no database is provided,
  118. it will attempt to open a connection using the variables in the CONFIGURABLE section
  119. above. If your scripts will be using the same username and password and database most of the time,
  120. just hard code the variables above so you can just call:
  121. $sql = new SqlObject() and it will open without filling out the constructor every time
  122. */
  123. public function __construct($username = NULL, $password = NULL, $database = NULL){
  124. if($username != NULL && $password != NULL && $database != NULL){
  125. $this->USERNAME = $username;
  126. $this->PASSWORD = $password;
  127. $this->DATABASE = $database;
  128. }
  129.  
  130. $this->connection = new mysqli($this->SERVER_ADDRESS, $this->USERNAME, $this->PASSWORD, $this->DATABASE_NAME, $this->SERVER_PORT);
  131.  
  132. if($this->connection->connect_error){
  133. throw new Exception("SqlObject::construct() Connection error: " . $this->connection->connect_error);
  134. }else{
  135. mysqli_set_charset($this->connection, $this->MYSQLI_CHARSET);
  136. if($this->connection->error){
  137. throw new Exception("SqlObject::construct() Charset Error: " . $this->connection->error);
  138. }
  139. }
  140. }
  141.  
  142. /*
  143. Use this function when a query is INSERT, UPDATE, etc
  144. where you know the SQL query is not going to return a row
  145. */
  146. public function setData($query){
  147. if($this->connection){
  148. $result = $this->connection->query($query);
  149. $this->last_query = $query;
  150. if($result == FALSE){
  151. /* How to handle certain SQL errors */
  152. switch($this->connection->errno){
  153. case self::ERR_DEADLOCK:
  154. return $this->retry_deadlock($query);
  155. break;
  156. case self::ERR_DUPLICATE:
  157. return FALSE;
  158. break;
  159. case self::ACCESS_DENIED_CHANGE_USER:
  160. throw new Exception($this->connection->error);
  161. break;
  162. case self::ACCESS_DENIED_USER:
  163. throw new Exception($this->connection->error);
  164. break;
  165. case self::ACCESS_DENIED_DATABASE:
  166. throw new Exception($this->connection->error);
  167. break;
  168. default:
  169. //file_put_contents('debug.txt', $this->connection->errno);
  170. throw new Exception($this->connection->error);
  171. break;
  172. }
  173. } //end error catching switch
  174. return TRUE; //Successful
  175. }
  176. return FALSE; //normally not reached
  177. }
  178.  
  179. /*
  180. This this to retrieve rows using an SQL query.
  181.  
  182. When returnType is SINGLE_ROW, only one row is returned even if there are many.
  183. When returnType is MULTI_ROW, an array of rows is returned.
  184.  
  185. NULL is returned if the query returns nothing.
  186. */
  187. public function getData($query, $returnType = self::SINGLE_ROW){
  188.  
  189. if(!$this->connection){
  190. throw new Exception('The Database connection is broken' . "n" . $query);
  191. }
  192.  
  193. /* Run the provided query, if the query is invalid the return will be FALSE */
  194. $datum = $this->connection->query($query);
  195. $this->last_query = $query;
  196.  
  197. if(!$datum){
  198. throw new Exception("SqlObject::getData() Error: " . $this->connection->error);
  199. }
  200.  
  201. /* Single row (0) returns an associative array of the selected row */
  202. /* Multi row (1) returns an array of associative array(s) from the query */
  203. switch($returnType){
  204. case self::SINGLE_ROW:
  205. $result = $datum->fetch_assoc();
  206. $datum->free();
  207. return $result;
  208. break;
  209. case self::MULTI_ROW:
  210. $this->last_count = 0;
  211. $returnData = array();
  212. while($nextRow = $datum->fetch_assoc()){
  213. array_push($returnData, $nextRow);
  214. $this->last_count++;
  215. }
  216. $datum->free();
  217. if($this->last_count > 0){
  218. return $returnData;
  219. }
  220. break;
  221. default:
  222. throw new Exception("Invalid argument for setData(); expected returnType is invalid");
  223. break;
  224. }
  225. return NULL;
  226. }
  227.  
  228.  
  229. /*
  230. Returns an array of strings containing all of the table names in the current database
  231. */
  232. public function get_table_names($filter = NULL){
  233. $query = $filter != NULL ? "SHOW TABLES LIKE '$filter'" : 'SHOW TABLES';
  234. $datum = $this->connection->query($query);
  235.  
  236. //$this->last_query = $query;
  237. if(!$datum){
  238. throw new Exception($this->connection->error);
  239. }
  240.  
  241. $names = array();
  242. while($nextRow = $datum->fetch_array(MYSQLI_NUM)){
  243. if(count($datum) > 0){
  244. array_push($names, $nextRow[0]);
  245. }
  246. }
  247. $datum->free();
  248. return $names;
  249. }
  250.  
  251. /* gets the size of the last returned array from getData */
  252. function get_last_arraysize(){
  253. return $this->last_count;
  254. }
  255.  
  256. /* gets the number of rows affected by the last query */
  257. public function get_affected_rows(){
  258. return $this->connection->affected_rows;
  259. }
  260.  
  261. /* returns the ID of the last INSERT query
  262. NOTE this does not return the ID from an UPDATE query */
  263. public function get_last_insert_id(){
  264. return $this->connection->insert_id;
  265. }
  266.  
  267. /* returns the last query that was executed */
  268. public function get_last_query(){
  269. return $this->last_query;
  270. }
  271.  
  272. /* pass back the sql error number */
  273. public function get_error_number(){
  274. return $this->connection->errno;
  275. }
  276.  
  277. /* return the sql error message */
  278. public function get_error(){
  279. return $this->connection->error;
  280. }
  281.  
  282. /* get the current database name */
  283. public function get_database_name(){
  284. return $this->DATABASE_NAME;
  285. }
  286.  
  287. /* get the current username */
  288. public function get_username(){
  289. return $this->USERNAME;
  290. }
  291.  
  292. /* attempt to connect to a new database with optional different credentials */
  293. public function change_database($new_database, $username = NULL, $password = NULL){
  294. if(!$this->connection){
  295. throw new Exception("change_database($new_database) failed because of a broken connection");
  296. }
  297. if($this->connection->change_user($username != NULL ? $username : $this->USERNAME,
  298. $password != NULL ? $password : $this->PASSWORD, $new_database)){
  299. $this->DATABASE_NAME = $new_database;
  300. $this->USERNAME = $username != NULL ? $username : $this->USERNAME;
  301. $this->PASSWORD = $password != NULL ? $password : $this->PASSWORD;
  302. }else{
  303. throw new Exception($this->get_error());
  304. }
  305. }
  306.  
  307. /* end the sql connection */
  308. public function terminate(){
  309. if($this->connection){
  310. $this->connection->close();
  311. unset($this->connection);
  312. }
  313. }
  314.  
  315. /*
  316. Returns an SQL query string given a table name and an associative array
  317. of key -> value items to be included in the query
  318. */
  319. public static function insertQuery($table, $values){
  320. $query = "INSERT INTO `$table`(";
  321. $firstpart = '';
  322. $lastpart = '';
  323. $len = count($values);
  324. $i = 0;
  325. foreach($values as $key => $value){
  326. $firstpart .= ($i < $len - 1) ? "`$key`," : "`$key`) VALUES(";
  327. if(is_numeric($value) && $value[0] != '+'){
  328. $lastpart .= ($i < $len - 1) ? "$value," : "$value)";
  329. }else{
  330. $lastpart .= ($i < $len - 1) ? "'$value'," : "'$value')";
  331. }
  332. $i++;
  333. }
  334. return ($query . $firstpart . $lastpart);
  335. }
  336.  
  337. /*
  338. Returns an SQL UPDATE query string given a table name
  339. and an associative array of key -> value pairs for the update parameters.
  340.  
  341. It is up to the programmer to append the WHERE clause (if needed) to the returned string
  342. */
  343. public static function updateQuery($table, $values){
  344. $query = "UPDATE `$table` SET ";
  345. $num = count($values);
  346. $i = 0;
  347. foreach($values as $key => $value){
  348. if(is_numeric($value) && $value[0] != '+'){
  349. $query .= "`$key`=$value";
  350. }else{
  351. $query .= "`$key`='$value'";
  352. }
  353. if($i < $num - 1){
  354. $query .= ', ';
  355. }
  356. $i += 1;
  357. }
  358. return $query;
  359. }
  360.  
  361. /*
  362. A light scan of a string for common SQL injection commands.
  363. returns true or false if the string contains a commands
  364.  
  365. *** This function does not detect all types of SQL injection ***
  366. *** Combine this with sanitizeForQuery() for stronger protection ***
  367. */
  368. public static function isSqlSafe($str){
  369. $select_safe = stripos($str, "SELECT `") === FALSE && stripos($str, "SELECT *") === FALSE;
  370. $insert_safe = stripos($str, "INSERT INTO `") === FALSE && stripos($str, "INSERT INTO *") === FALSE;
  371. $update_safe = stripos($str, "UPDATE `") === FALSE && stripos($str, "UPDATE *") === FALSE;
  372. $join_safe = stripos($str, "JOIN `") === FALSE && stripos($str, "INNER JOIN `") === FALSE;
  373. return $select_safe && $insert_safe && $update_safe && $join_safe && $bogus_quote;
  374. }
  375.  
  376. /*
  377. Takes a string containing possibly harmful SQL text and characters and
  378. returns a sanitized version with those items removed.
  379.  
  380. $blacklist - an array of strings that should be included in the sanitization default blacklisted
  381. $whitelist - an array of strings that should be whitelisted
  382. */
  383. public static function sanitizeForQuery($str, $blacklist = NULL, $whitelist = NULL){
  384. //These phraises are to be removed from the resulting value
  385. $blacklist1 = array(
  386. '<?php',
  387. '?>',
  388. 'x',
  389. );
  390.  
  391. $str = SqlObject::escapeSpecialChars($str);
  392.  
  393. /* Loop through all of the blacklisted phraises and remove them */
  394. foreach($blacklist1 as $blockedchar){
  395. if($whitelist !== NULL){
  396. if(!in_array($blockedchar, $whitelist)){
  397. $temp = str_replace($blockedchar, '', $str);
  398. unset($str);
  399. $str = $temp;
  400. }
  401. }else{
  402. $temp = str_replace($blockedchar, '', $str);
  403. unset($str);
  404. $str = $temp;
  405. }
  406. }
  407.  
  408. if($blacklist !== NULL){
  409. foreach($blacklist as $filter){
  410. $temp = str_replace($filter, '', $str);
  411. unset($str);
  412. $str = $temp;
  413. }
  414. }
  415.  
  416. return $str;
  417. }
  418.  
  419. /*
  420. Escape single quote, double quote, percent, underline (_)
  421. also replaces multiple consecutive \ slashes with a single one
  422. so escapes dont get messed up
  423. */
  424. public static function escapeSpecialChars($str){
  425. $temp = preg_replace("/\'+/", "'", $str);
  426. $temp = preg_replace('/"+/', '"', $temp);
  427. $temp = preg_replace('/%+/', '%', $temp);
  428. $temp = preg_replace('/_+/', '_', $temp);
  429. return preg_replace('/\+/', '\', $temp);
  430. }
  431.  
  432. /* automatically close the connection when the object is disposed */
  433. public function __destruct(){
  434. if(isset($this->connection)){
  435. $this->connection->close();
  436. unset($this->connection);
  437. }
  438. }
  439.  
  440. /* reattempt to run the query that caused a deadlock */
  441. private function retry_deadlock($query){
  442. for($i = 0; $i < self::RETRY_DEADLOCK_ATTEMPTS; $i++){
  443. $result = $this->connection->query($query);
  444. if($result){
  445. return TRUE;
  446. }
  447. sleep(1);
  448. }
  449. return FALSE;
  450. }
  451.  
  452. } /* end class */
  453.  
  454.  
  455. ?>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!