ona  18.1.1
About: OpenNetAdmin provides a database managed inventory of your IP network (with Web and CLI interface).
  Fossies Dox: ona-18.1.1.tar.gz  ("inofficial" and yet experimental doxygen-generated source code documentation)  

sql.inc.php
Go to the documentation of this file.
1 <?php
2 
4 // Function: ona_sql (string $options='')
5 //
6 // Input Options:
7 // $options = key=value pairs of options for this function.
8 // multiple sets of key=value pairs should be separated
9 // by an "&" symbol.
10 //
11 // Output:
12 // Returns a two part list:
13 // 1. The exit status of the function (0 on success, non-zero on error)
14 // 2. A textual message for display on the console or web interface.
15 //
17 function ona_sql($options="") {
18 
19  // The important globals
20  global $conf, $onadb, $base;
21 
22  // Version - UPDATE on every edit!
23  $version = '1.05';
24 
25  // TODO: Maybe make this into a sys_config option
26  $srvdir = dirname($base)."/sql";
27 
28  printmsg('DEBUG => ona_sql('.$options.') called', 3);
29 
30  // Parse incoming options string to an array
31  $options = parse_options($options);
32 
33  // Sanitize delimeter
34  if (!$options['delimiter']) {
35  $options['delimiter'] = ':';
36  }
37 
38  // fix up the escaped ' marks. may need the = and & stuff too????
39  $options['sql'] = str_replace('\\\'','\'',$options['sql']);
40  $options['sql'] = str_replace('\\=','=',$options['sql']);
41 
42  // Set "options[commit] to no if it's not set
43  if (!array_key_exists('commit', $options)) {
44  $options['commit'] = 'N';
45  }
46  // Otherwise sanitize it's value
47  else {
48  $options['commit'] = sanitize_YN($options['commit'], 'N');
49  }
50 
51  // Set "options[commit] to no if it's not set
52  if (!array_key_exists('dataarray', $options)) {
53  $options['dataarray'] = 'N';
54  }
55  // Otherwise sanitize it's value
56  else {
57  $options['dataarray'] = sanitize_YN($options['dataarray'], 'N');
58  }
59 
60  // Set "options[header] to yes if it's not set
61  if (!array_key_exists('header', $options)) {
62  $options['header'] = 'Y';
63  }
64  // Otherwise sanitize it's value
65  else {
66  $options['header'] = sanitize_YN($options['header'], 'Y');
67  }
68 
69  // Check permissions
70  if (!auth('ona_sql')) {
71  $self['error'] = "Permission denied!";
72  printmsg($self['error'], 0);
73  return(array(10, $self['error'] . "\n"));
74  }
75 
76  // Return the usage summary if we need to
77  if ($options['help'] or !(($options['list'] and !$options['sql']) or (!$options['list'] and $options['sql']))) {
78  // NOTE: Help message lines should not exceed 80 characters for proper display on a console
79  return(array(1,
80 <<<EOM
81 
83 Runs the specified SQL query on the database and prints the result
84 
85  Synopsis: ona_sql [KEY=VALUE] ...
86 
87  Required:
88  sql=STATEMENT|FILENAME quoted SQL statement to execute
89  OR
90  list lists the SQL files available on the server side
91 
92  Optional:
93  show displays contents of SQL, gives usage etc
94  commit=yes|no commit the transaction (no)
95  header=yes|no display record header (yes)
96  delimiter=DELIMITER record delimiter for output (:)
97  (1,2,..)=VALUE bind variables, replaces ? in query sequentially.
98  the first ? found is replaced by 1=value, and so on
99 
100  Notes:
101  * Query is sent to the configured OpenNetAdmin database server.
102  * The use of bind variables requires your options to match positionally.
103  * The SQL option will be tried first as a local file, then as a server
104  file, then as a raw text SQL query. Filenames are case sensitive.
105  * Server based SQL files are located in {$srvdir}
106  * Some plugins may provide their own SQL dir inside the plugin directory
107  * Use the show option to display contents of SQL files, this should contain
108  a long description and any usage information that is needed.
109 \n
110 EOM
111 
112  ));
113  }
114 
115  // TODO: check that the user has admin privs? or at least a ona_sql priv
116 
117  // Get a list of the files
118  $plugins = plugin_list();
119  $files = array();
120  $srvdirs = array();
121  array_push($srvdirs, $srvdir);
122  // add a local sql dir as well so they don't get overrriden by installs
123  array_push($srvdirs, dirname($base).'/www/local/sql');
124 
125  // loop through the plugins and find files inside of their sql directories.
126  foreach($plugins as $plug) {
127  array_push($srvdirs, $plug['path'].'/sql');
128  }
129 
130  // Loop through each of our plugin directories and the default directory to find .sql files
131  foreach ($srvdirs as $srvdir) {
132  if ($handle = @opendir($srvdir)) {
133  while (false !== ($file = readdir($handle))) {
134  if ($file != "." && $file != ".." && substr($file, -4) == '.sql') {
135  // Build an array of filenames
136  array_push($files, $srvdir.'/'.$file);
137  }
138  }
139  closedir($handle);
140  }
141  }
142 
143  // sort the file names
144  asort($files);
145 
146  // List the sql files on the server side
147  if ($options['list'] == 'Y') {
148  $text .= sprintf("\n%-25s%s\n",'FILE','DESCRIPTION');
149  $text .= sprintf("%'-80s\n",'');
150 
151  // Loop through and display info about the files
152  foreach($files as $file) {
153  // Open the file and get the first line, this is the short description
154  $fh = fopen($file, 'r');
155  $desc = rtrim(fgets($fh));
156  fclose($fh);
157 
158  // Print the info
159  $text .= sprintf("%-25s%s\n",basename($file),$desc);
160  }
161  $text .= "\n";
162 
163 
164  return(array(0, $text));
165  }
166 
167  // Check that the sql variable passsed matches a file name locally, if it does, open it and replace $options['sql'] with it
168  // Loop through files array till we find the right file
169  $foundfile=false;
170  foreach($files as $file) {
171  if (strstr($file,$options['sql'])) {
172  $options['sql'] = trim(file_get_contents($file));
173  $foundfile=true;
174  }
175  }
176 
177  // if we have not found a file on the server and the sql option does end in .sql then print a message that we coulnt find a file
178  // otherwise assume it is a sql statement being passed at the cli
179  if($foundfile==false and substr($options['sql'], -4) == '.sql') {
180  $self['error'] = "ERROR => Unable to find specified SQL stored on server: {$options['sql']}";
181  printmsg($self['error'],2);
182  return(array(10, $self['error']."\n"));
183  }
184 
185  // Show the contents of the sql query for usage info etc.
186  if ($options['show'] == 'Y') {
187  $text .= $options['sql']."\n\n";
188 
189  return(array(0, $text));
190  }
191 
192  // Count how many ?s there are in the sql query. that must match how many sqlopts are passed
193  // if this is an oracle database you could change the ? to a :.. more work on this however needs to be done
194  $qvars = substr_count($options['sql'], '?');
195 
196  // loop through the options based on how many qvars are in the sql statement. print an error if we didnt
197  // get a variable to use in the sql statement
198  for ($i = 1; $i <= $qvars; $i++) {
199  if (!array_key_exists($i,$options)) {
200  $self['error'] = "ERROR => You did not supply a value for bind variable {$i}!";
201  printmsg($self['error'],2);
202  return(array(10, $self['error']."\n"));
203  }
204  // assign the variables to sqlopts
205  $sqlopts[$i] = $options[$i];
206  }
207 
208  // One last check to be sure
209  // Count how many times ? is in the sql statement. there should be that many elements in sqlopts
210  if (count($sqlopts) != $qvars) {
211  $self['error'] = "ERROR => SQL query and bind variable count did not match.";
212  printmsg($self['error'],2);
213  return(array(1, $self['error']."\n"));
214  }
215 
216 
217  printmsg("DEBUG => [ona_sql] Running SQL query: {$options['sql']}",5);
218 
219  // Run the query
220  $rs = $onadb->Execute($options['sql'],$sqlopts);
221 
222  if ($rs === false) {
223  $self['error'] = "ERROR => SQL query failed: " . $onadb->ErrorMsg() . "\n";
224  return(array(2, $self['error']));
225  }
226 
227  $text = "";
228  $dataarr = array();
229 
230  // If we got a record, that means they did a select .. display it
231  if ($rs->RecordCount()) {
232  $build_header = 1;
233  $i=0;
234  // Loop through each record returned by the sql query
235  while (!$rs->EOF) {
236  $i++;
237  $record = $rs->FetchRow();
238 
239  $dataarr[$i] = $record;
240 
241 
242  // Build the header if we need to
243  if ($build_header == 1 and $options['header'] == 'Y') {
244  $build_header = 0;
245  foreach (array_keys($record) as $key) {
246  $text .= $key . $options['delimiter'];
247  }
248  $text = preg_replace("/{$options['delimiter']}$/", "", $text);
249  $text .= "\n";
250  }
251 
252  // Display the row
253  foreach (array_keys($record) as $key) {
254  $text .= $record[$key] . $options['delimiter'];
255  }
256  $text = preg_replace("/{$options['delimiter']}$/", "", $text);
257  $text .= "\n";
258  }
259  }
260 
261  else {
262  $text .= "NOTICE => SQL executed successfully - no records returned\n";
263  }
264 
265  // If we want the recordset returned instead of the text
266  if ($options['dataarray'] == 'Y') {
267  return(array(0, $dataarr));
268  }
269 
270  // Unless the user said YES to commit, return a non-zero
271  // exit status so that module_run.php doesn't commit the DB transaction.
272  $return = 1;
273  if ($options['commit'] == 'Y') {
274  $return = 0;
275  }
276 
277  return(array($return, $text));
278 }
279 
280 
281 
282 
283 
284 
285 
286 ?>
sanitize_YN
sanitize_YN($string="", $default="Y")
Definition: functions_general.inc.php:1637
$rs
if(isset($_REQUEST['nrows'])) else $rs
Definition: server.php:94
$record
$record['display_name']
Definition: app_advanced_search.inc.php:12
$onadb
global $onadb
Definition: 2-to-3.php:15
printmsg
if(6<=$conf['debug']) printmsg($msg="", $debugLevel=0)
Definition: functions_general.inc.php:48
ona_sql
ona_sql($options="")
Definition: sql.inc.php:17
$conf
global $conf
Definition: 2-to-3.php:15
$text
$text
Definition: install.php:11
$self
global $self
Definition: 2-to-3.php:15
auth
auth($resource, $msg_level=1)
Definition: functions_general.inc.php:1330
$base
$base
Definition: 2-to-3.php:8
parse_options
parse_options($options="")
Definition: functions_general.inc.php:1579
$version
$version
Definition: main.inc.php:22
plugin_list
plugin_list($type='')
Definition: functions_gui.inc.php:62