[Summary view] [Print] [Text view]

   1  <?php
   2  /*#################################################################
   3  #
   4  # Name:    sql.php
   5  #
   6  # Description:
   7  #   SQL related routines for SQLDIFF
   8  #
   9  # Copyright (C) 2001-2006 Dmitriy Katsman, Terry Gliedt, University of Michigan
  10  # This is free software; you can redistribute it and/or modify it under the
  11  # terms of the GNU General Public License as published by the Free Software
  12  # Foundation; See http://www.gnu.org/copyleft/gpl.html
  13  #################################################################*/
  14  
  15  /*---------------------------------------------------------------
  16  # do_sql - Generate SQL to modify sql_table
  17  #
  18  # Parameters:
  19  #   $sql - SQL command to execute
  20  #
  21  # Returns:
  22  #   an error message or "OK' if successful
  23  #   
  24  ---------------------------------------------------------------*/
  25  function do_sql($sql) {
  26      global $dbh;
  27  
  28      //  All set up, get connection
  29      db_connect($_SESSION['sql_type'], $_SESSION['sql_host'], $_SESSION['sql_user'],
  30          $_SESSION['sql_pass'], $_SESSION['sql_db']);
  31  
  32      //  Do SQL we have constructed. Be sure we know when it fails.
  33      //  DELETE must return 1 row affected. UPDATE/INSERT return 0 :-(
  34      $res = $dbh->query($sql);
  35      //if (DB::isError($res)) { $rows = 0; }
  36      //else { $rows = $res->affectedRows(); }
  37      $rows = $dbh->affectedRows();
  38      $fail = 0;
  39      if (! preg_match("/^(INSERT|DELETE|UPDATE)/", $sql)) { $fail = 10; }
  40      elseif (preg_match("/^INSERT/", $sql) && DB::isError($res)) { $fail = 1; }
  41      elseif (preg_match("/^DELETE/", $sql) && $rows != 1) { $fail = 1; }
  42      elseif (preg_match("/^UPDATE/", $sql) && DB::isError($res)) { $fail = 1; }
  43      if ($fail) {
  44          $errmsg = "SQL command failed for <b>'" . $_SESSION['sql_table'] .
  45              "'</b>\n" . "SQL=$sql<br/><br/>\n";
  46          if ($fail < 10) { $errmsg .= $res->getMessage() . ": " . $res->getDebugInfo(); }
  47          $errmsg .= "\n";
  48          return $errmsg;
  49      }
  50  
  51      if ($_SESSION['showdetails']) { redmsg("SQL=$sql updated $rows rows<br/><br/>\n"); }
  52      return 'OK';
  53  }
  54  
  55  /*---------------------------------------------------------------
  56  # build_sql - Returns a string of UPDATE SQL. You must have
  57  #   called get_desc() so that $desc1 is set.
  58  #
  59  # Parameters:
  60  #   prikey - primary key column name
  61  #   prival - primary key value
  62  #   whatcols - ($addcols, $chgcols, etc)
  63  #   row - delimited row of data
  64  #   $cmd - SQL command to do (insert, update etc)
  65  #
  66  # Returns:
  67  #   SQL statement or '' if prikey was not in sql_table
  68  #   
  69  ---------------------------------------------------------------*/
  70  function build_sql($prikey, $prival, $whatcols, $row, $cmd) {
  71      global $LCFG;
  72  
  73      $cmd = strtoupper($cmd);
  74      if (! $whatcols) { nice_exit("build_sql: cmd=$cmd, no column names provided\n"); }
  75      $sql = '';    
  76      $rowdata = explode($LCFG['delimiter'], $row);
  77      if ($prival != $rowdata[0]) { nice_exit("Primary key '$prikey=$prival' not in row ($row)"); }
  78  
  79      //  Generate an insert into sql_table from sql_table2
  80      if ($cmd == 'INSERT') {
  81          $desc = get_desc($_SESSION['sql_type2'], $_SESSION['sql_host2'],
  82              $_SESSION['sql_user2'], $_SESSION['sql_pass2'], $_SESSION['sql_db2'],
  83              $_SESSION['sql_table2']);
  84          $prikey=$desc['_prikey_'];
  85          $sql = "INSERT INTO " . $_SESSION['sql_table'] . " ($whatcols) VALUES(" .
  86              get_sql_value($prival, $desc[$prikey]) . ',';
  87          $cols = explode(',', $whatcols);
  88          if (count($cols) < 1) { nice_exit("build_sql: Invalid columns '$whatcols'\n"); }
  89          for ($j=1; $j<count($cols); $j++) {
  90              $val = get_sql_value($rowdata[$j], $desc[$cols[$j]]);
  91              $sql .= "$val,";
  92          }
  93          $sql = substr($sql,0,strlen($sql)-1);   // Drop trailing comma
  94          $sql .= ")"; 
  95          if ($_SESSION['showdetails']) { redmsg("SQL=$sql<br/><br/>\n"); }
  96          return $sql;
  97      }
  98      
  99      //  Generate an update of sql_table from sql_table2
 100      if ($cmd == 'UPDATE') {
 101          $desc = get_desc($_SESSION['sql_type'], $_SESSION['sql_host'],
 102              $_SESSION['sql_user'], $_SESSION['sql_pass'], $_SESSION['sql_db'],
 103              $_SESSION['sql_table']);
 104          $prikey=$desc['_prikey_'];
 105          $sql = "UPDATE " . $_SESSION['sql_table'] . " SET ";
 106          $cols = explode(',', $whatcols);
 107          if (count($cols) < 1) { nice_exit("build_sql: Invalid columns '$whatcols'\n"); }
 108          for ($j=1; $j<count($cols); $j++) {
 109              $val = get_sql_value($rowdata[$j], $desc[$cols[$j]]);
 110              $sql .= "$cols[$j]=$val,";
 111          }
 112          $sql = substr($sql,0,strlen($sql)-1);   // Drop trailing comma
 113          $sql .= " WHERE $prikey=". get_sql_value($prival, $desc[$prikey]); 
 114          if ($_SESSION['showdetails']) { redmsg("SQL=$sql<br/><br/>\n"); }
 115          return $sql;
 116      }
 117      
 118      //  Generate a delete of data in sql_table
 119      if ($cmd == 'DELETE') {
 120          $desc = get_desc($_SESSION['sql_type'], $_SESSION['sql_host'],
 121              $_SESSION['sql_user'], $_SESSION['sql_pass'], $_SESSION['sql_db'],
 122              $_SESSION['sql_table']);
 123          $prikey=$desc['_prikey_'];
 124          $sql = "DELETE FROM " . $_SESSION['sql_table'] . " ";
 125          $sql .= "WHERE $prikey=". get_sql_value($prival, $desc[$prikey]); 
 126          if ($_SESSION['showdetails']) { redmsg("SQL=$sql<br/><br/>\n"); }
 127          return $sql;
 128      }
 129      
 130      nice_exit("build_sql: Invalid type of SQL '$cmd'");   // Should never get here
 131  }
 132  
 133  /*---------------------------------------------------------------
 134  # get_sql_value - Returns a string ready for use in an SQL statement
 135  #
 136  # Parameters:
 137  #   val - value to handle
 138  #   type - type of data $val is
 139  #
 140  # Returns:
 141  #   string suitable for use in an SQL statement
 142  #   
 143  ---------------------------------------------------------------*/
 144  function get_sql_value($val, $type) {
 145  
 146      //  Empty things should become NULL, not ''
 147      if ($val == '') { return 'NULL'; }
 148  
 149      //  Based on the type of data, return a quoted value (or not)
 150      if (preg_match('/char/i', $type)) {
 151          $val = stripslashes($val);      // Canonicalize data first
 152          return "'" . addslashes($val) . "'";
 153      }
 154      if (preg_match('/text/i', $type)) {
 155          $val = stripslashes($val);      // Canonicalize data first
 156          return "'" . addslashes($val) . "'";
 157      }
 158      if (preg_match('/blob/i', $type)) {
 159          $val = stripslashes($val);      // Canonicalize data first
 160          return "'" . addslashes($val) . "'";
 161      }
 162      if (preg_match('/string/i', $type)) {
 163          $val = stripslashes($val);      // Canonicalize data first
 164          return "'" . addslashes($val) . "'";
 165      }
 166      if (preg_match('/date/i', $type)) { return "'" . $val . "'"; }
 167      if (preg_match('/time/i', $type)) { return "'" . $val . "'"; }
 168      if (preg_match('/year/i', $type)) { return "'" . $val . "'"; }
 169  
 170      return $val;
 171  }
 172  
 173  ?>