com web/qa: Use prepared statements: reports/details.php reports/parserfunc.php reports/run_tests.php reports/view reports.php
| From: | Christoph Michael Becker | Date: | Wed, 25 Jul 2018 14:06:14 +0000 |
| Subject: | com web/qa: Use prepared statements: reports/details.php reports/parserfunc.php reports/run_tests.php reports/view reports.php | ||
| Groups: | php.qa | ||
| Request: | Send a blank email to php-qa+get-68139@lists.php.net to get a copy of this message | ||
Commit: 77ab09143f7d7cb941db5aa256d9290487a9a3ec
Author: Christoph M. Becker <cmbecker69@gmx.de> Wed, 25 Jul 2018 16:06:14 +0200
Parents: 0452e9b401061e97f099ce8af2d5feff7f420274
Branches: master
Link: http://git.php.net/?p=web/qa.git;a=commitdiff;h=77ab09143f7d7cb941db5aa256d9290487a9a3ec
Log:
Use prepared statements
“Oh, yes. Little Bobby Tables, we call him.”
Changed paths:
M reports/details.php
M reports/parserfunc.php
M reports/run_tests.php
M reports/viewreports.php
diff --git a/reports/details.php b/reports/details.php index 80709c2..0a6700d 100644 --- a/reports/details.php +++ b/reports/details.php @@ -41,15 +41,19 @@ if (!$database) { } // GET infos from DB -$query = 'SELECT reports.* FROM failed JOIN reports ON reports.id=failed.id_report WHERE signature=X\''.$signature.'\''; - -$q = $database->query($query); +$query = 'SELECT reports.* FROM failed JOIN reports ON reports.id=failed.id_report WHERE signature=:signature'; +$stmt = $database->prepare($query); +$stmt->bindValue(':signature', hex2bin($signature), SQLITE3_BLOB); +$q = $stmt->execute(); $reportsArray = array(); while ($tab = $q->fetchArray(SQLITE3_ASSOC)) { $reportsArray[$tab['id']] = $tab; } -$tab = $database->query('SELECT test_name FROM failed WHERE signature=X\''.$signature.'\' LIMIT 1'); +$query = 'SELECT test_name FROM failed WHERE signature=:signature LIMIT 1'; +$stmt = $database->prepare($query); +$stmt->bindValue(':signature', hex2bin($signature), SQLITE3_BLOB); +$tab = $database->query($query); list($testName) = $tab->fetchArray(SQLITE3_NUM); // We stop everything diff --git a/reports/parserfunc.php b/reports/parserfunc.php index 519ef0a..82ea966 100644 --- a/reports/parserfunc.php +++ b/reports/parserfunc.php @@ -108,19 +108,28 @@ function insertToDb_phpmaketest($array, $QA_RELEASES = array()) // handle tests with no success if (!isset($array['succeededTest'])) $array['succeededTest'] = array(); - $query = "INSERT INTO `reports` (`id`, `date`, `status`, - `nb_failed`, `nb_expected_fail`, `success`, `build_env`, `phpinfo`, user_email) VALUES (null, - datetime(".((int) $array['date']).", 'unixepoch', 'localtime'), - ".((int)$array['status']).", - ".count($array['failedTest']).", - ".count($array['expectedFailedTest']).", - ".count($array['succeededTest']).", - ('".$dbi->escapeString($array['buildEnvironment'])."'), - ('".$dbi->escapeString($array['phpinfo'])."'), - ".(!$array['userEmail'] ? "NULL" : "'".$dbi->escapeString($array['userEmail'])."'")." - )"; - - $dbi->query($query); + $query = <<<'SQL' +INSERT INTO `reports` ( + `id`, `date`, `status`, `nb_failed`, `nb_expected_fail`, `success`, `build_env`, `phpinfo`, `user_email` +) VALUES ( + null, datetime(:date, 'unixepoch', 'localtime'), :status, :nb_failed, + :nb_expected_fail, :success, :build_env, :phpinfo, :user_email +) +SQL; + $stmt = $dbi->prepare($query); + $stmt->bindValue(':date', (int) $array['date'], SQLITE3_INTEGER); + $stmt->bindValue(':status', (int)$array['status'], SQLITE3_INTEGER); + $stmt->bindValue(':nb_failed', count($array['failedTest']), SQLITE3_INTEGER); + $stmt->bindValue(':nb_expected_fail', count($array['expectedFailedTest']), SQLITE3_INTEGER); + $stmt->bindValue(':success', count($array['succeededTest']), SQLITE3_INTEGER); + $stmt->bindValue(':build_env', $array['buildEnvironment'], SQLITE3_TEXT); + $stmt->bindValue(':phpinfo', $array['phpinfo'], SQLITE3_TEXT); + if (!$array['userEmail']) { + $stmt->bindValue(':user_email', NULL, SQLITE3_NULL); + } else { + $stmt->bindValue(':user_email', $array['userEmail'], SQLITE3_TEXT); + } + $stmt->execute(); if ($dbi->lastErrorCode() != '') { echo "ERROR: ".$dbi->lastErrorMsg()."\n"; exit; @@ -132,13 +141,17 @@ function insertToDb_phpmaketest($array, $QA_RELEASES = array()) if (substr($name, 0, 1) != '/') $name = '/'.$name; $test = $array['tests'][$name]; - $query = "INSERT INTO `failed` - (`id`, `id_report`, `test_name`, signature, `output`, `diff`) VALUES (null, - '".$reportId."', '".$name."', - X'".md5($name.'__'.$test['diff'])."', - ('".$dbi->escapeString($test['output'])."'), ('".$dbi->escapeString($test['diff'])."'))"; - - @$dbi->query($query); + $query = <<<'SQL' +INSERT INTO `failed` (`id`, `id_report`, `test_name`, `signature`, `output`, `diff`) +VALUES (null, :id_report, :test_name, :signature, :output, :diff) +SQL; + $stmt = $dbi->prepare($query); + $stmt->bindValue(':id_report', $reportId, SQLITE3_INTEGER); + $stmt->bindValue(':test_name', $name, SQLITE3_TEXT); + $stmt->bindValue(':signature', md5($name.'__'.$test['diff'], true), SQLITE3_BLOB); + $stmt->bindValue(':output', $test['output'], SQLITE3_TEXT); + $stmt->bindValue(':diff', $test['diff'], SQLITE3_TEXT); + @$stmt->execute(); if ($dbi->lastErrorCode() != '') { echo "ERROR when inserting failed test : ".$dbi->lastErrorMsg()."\n"; exit; @@ -146,10 +159,14 @@ function insertToDb_phpmaketest($array, $QA_RELEASES = array()) } foreach ($array['expectedFailedTest'] as $name) { - $query = "INSERT INTO `expectedfail` - (`id`, `id_report`, `test_name`) VALUES (null, '".$reportId."', '".$name."')"; - - @$dbi->query($query); + $query = <<<'SQL' +INSERT INTO `expectedfail` (`id`, `id_report`, `test_name`) +VALUES (null, :id_report, :test_name) +SQL; + $stmt = $dbi->prepare($query); + $stmt->bindValue(':id_report', $reportId, SQLITE3_INTEGER); + $stmt->bindValue(':test_name', $name, SQLITE3_TEXT); + @$stmt->execute(); if ($dbi->lastErrorCode() != '') { echo "ERROR when inserting expected fail test : ".$dbi->lastErrorMsg()."\n"; exit; @@ -158,16 +175,23 @@ function insertToDb_phpmaketest($array, $QA_RELEASES = array()) foreach ($array['succeededTest'] as $name) { // sqlite files too big .. For the moment, keep only one success over time - $res = $dbi->query('SELECT id, id_report FROM `success` WHERE test_name LIKE \''. - $dbi->escapeString($name).'\''); + $query = 'SELECT id, id_report FROM `success` WHERE test_name LIKE :name'; + $stmt = $dbi->prepare($query); + $stmt->bindValue(':name', $name, SQLITE3_TEXT); + $res = $stmt->execute(); if ($res->numColumns() > 0) { // hit ! do nothing atm } else { - $query = "INSERT INTO `success` (`id`, `id_report`, `test_name`) - VALUES (null, '".$reportId."', '".$dbi->escapeString($name)."')"; + $query = <<<'SQL' +INSERT INTO `success` (`id`, `id_report`, `test_name`) +VALUES (null, :id_report, :test_name) +SQL; + $stmt = $dbi->prepare($query); + $stmt->bindValue(':id_report', $reportId, SQLITE3_INTEGER); + $stmt->bindValue(':test_name', $name, SQLITE3_TEXT); - @$dbi->query($query); + @$stmt->execute(); if ($dbi->lastErrorCode() != '') { echo "ERROR when inserting succeeded test : ".$dbi->lastErrorMsg()."\n"; exit; diff --git a/reports/run_tests.php b/reports/run_tests.php index 1b80a47..d611063 100644 --- a/reports/run_tests.php +++ b/reports/run_tests.php @@ -54,8 +54,10 @@ if (isset($_GET['version'])) { if (isset($_GET['expect']) && $_GET['expect'] == 1) { $query = 'SELECT \'xfail\' as xfail, test_name,COUNT(expectedfail.id) as cpt,\'-\' as variations, datetime(date) as date FROM expectedfail,reports WHERE expectedfail.id_report = reports.id - GROUP BY test_name ORDER BY cpt DESC LIMIT ' . $limit; - $q = @$database->query($query); + GROUP BY test_name ORDER BY cpt DESC LIMIT :limit'; + $stmt = $database->prepare($query); + $stmt->bindValue(':limit', $limit, SQLITE3_INTEGER); + $q = @$stmt->execute(); if ($q) { while ($tab = $q->fetchArray(SQLITE3_ASSOC)) { $failedTestsArray[] = $tab; @@ -69,8 +71,10 @@ if (isset($_GET['version'])) { LEFT JOIN failed f2 ON (f2.test_name=failed.test_name AND f2.output = "") LEFT JOIN reports r2 ON (f2.id_report = r2.id AND r2.user_email="ciqa") WHERE failed.id_report = reports.id - GROUP BY failed.test_name ORDER BY cpt DESC LIMIT ' . $limit; - $q = @$database->query($query); + GROUP BY failed.test_name ORDER BY cpt DESC LIMIT :limit'; + $stmt = $database->prepare($query); + $stmt->bindValue(':limit', $limit, SQLITE3_INTEGER); + $q = @$stmt->execute(); if (!$q) die("Error querying DB (error ".$database->lastErrorCode()."): ".$database->lastErrorMsg()); while ($tab = $q->fetchArray(SQLITE3_ASSOC)) { $failedTestsArray[] = $tab; diff --git a/reports/viewreports.php b/reports/viewreports.php index cde827b..f128514 100644 --- a/reports/viewreports.php +++ b/reports/viewreports.php @@ -42,10 +42,11 @@ if (!$database) { // GET infos from DB $query = 'SELECT id,signature, COUNT(*) as cpt, output, diff FROM failed -WHERE test_name="'.$database->escapeString($testName).'" +WHERE test_name=:test_name GROUP BY diff ORDER BY COUNT(*) desc'; - -$q = $database->query($query); +$stmt = $database->prepare($query); +$stmt->bindValue(':test_name', $testName, SQLITE3_TEXT); +$q = $stmt->execute(); $allDiffArray = array(); $sumCount = 0; while ($tab = $q->fetchArray(SQLITE3_ASSOC)) {
diff --git a/reports/details.php b/reports/details.php index 80709c2..0a6700d 100644 --- a/reports/details.php +++ b/reports/details.php @@ -41,15 +41,19 @@ if (!$database) { } // GET infos from DB -$query = 'SELECT reports.* FROM failed JOIN reports ON reports.id=failed.id_report WHERE signature=X\''.$signature.'\''; - -$q = $database->query($query); +$query = 'SELECT reports.* FROM failed JOIN reports ON reports.id=failed.id_report WHERE signature=:signature'; +$stmt = $database->prepare($query); +$stmt->bindValue(':signature', hex2bin($signature), SQLITE3_BLOB); +$q = $stmt->execute(); $reportsArray = array(); while ($tab = $q->fetchArray(SQLITE3_ASSOC)) { $reportsArray[$tab['id']] = $tab; } -$tab = $database->query('SELECT test_name FROM failed WHERE signature=X\''.$signature.'\' LIMIT 1'); +$query = 'SELECT test_name FROM failed WHERE signature=:signature LIMIT 1'; +$stmt = $database->prepare($query); +$stmt->bindValue(':signature', hex2bin($signature), SQLITE3_BLOB); +$tab = $database->query($query); list($testName) = $tab->fetchArray(SQLITE3_NUM); // We stop everything diff --git a/reports/parserfunc.php b/reports/parserfunc.php index 519ef0a..82ea966 100644 --- a/reports/parserfunc.php +++ b/reports/parserfunc.php @@ -108,19 +108,28 @@ function insertToDb_phpmaketest($array, $QA_RELEASES = array()) // handle tests with no success if (!isset($array['succeededTest'])) $array['succeededTest'] = array(); - $query = "INSERT INTO `reports` (`id`, `date`, `status`, - `nb_failed`, `nb_expected_fail`, `success`, `build_env`, `phpinfo`, user_email) VALUES (null, - datetime(".((int) $array['date']).", 'unixepoch', 'localtime'), - ".((int)$array['status']).", - ".count($array['failedTest']).", - ".count($array['expectedFailedTest']).", - ".count($array['succeededTest']).", - ('".$dbi->escapeString($array['buildEnvironment'])."'), - ('".$dbi->escapeString($array['phpinfo'])."'), - ".(!$array['userEmail'] ? "NULL" : "'".$dbi->escapeString($array['userEmail'])."'")." - )"; - - $dbi->query($query); + $query = <<<'SQL' +INSERT INTO `reports` ( + `id`, `date`, `status`, `nb_failed`, `nb_expected_fail`, `success`, `build_env`, `phpinfo`, `user_email` +) VALUES ( + null, datetime(:date, 'unixepoch', 'localtime'), :status, :nb_failed, + :nb_expected_fail, :success, :build_env, :phpinfo, :user_email +) +SQL; + $stmt = $dbi->prepare($query); + $stmt->bindValue(':date', (int) $array['date'], SQLITE3_INTEGER); + $stmt->bindValue(':status', (int)$array['status'], SQLITE3_INTEGER); + $stmt->bindValue(':nb_failed', count($array['failedTest']), SQLITE3_INTEGER); + $stmt->bindValue(':nb_expected_fail', count($array['expectedFailedTest']), SQLITE3_INTEGER); + $stmt->bindValue(':success', count($array['succeededTest']), SQLITE3_INTEGER); + $stmt->bindValue(':build_env', $array['buildEnvironment'], SQLITE3_TEXT); + $stmt->bindValue(':phpinfo', $array['phpinfo'], SQLITE3_TEXT); + if (!$array['userEmail']) { + $stmt->bindValue(':user_email', NULL, SQLITE3_NULL); + } else { + $stmt->bindValue(':user_email', $array['userEmail'], SQLITE3_TEXT); + } + $stmt->execute(); if ($dbi->lastErrorCode() != '') { echo "ERROR: ".$dbi->lastErrorMsg()."\n"; exit; @@ -132,13 +141,17 @@ function insertToDb_phpmaketest($array, $QA_RELEASES = array()) if (substr($name, 0, 1) != '/') $name = '/'.$name; $test = $array['tests'][$name]; - $query = "INSERT INTO `failed` - (`id`, `id_report`, `test_name`, signature, `output`, `diff`) VALUES (null, - '".$reportId."', '".$name."', - X'".md5($name.'__'.$test['diff'])."', - ('".$dbi->escapeString($test['output'])."'), ('".$dbi->escapeString($test['diff'])."'))"; - - @$dbi->query($query); + $query = <<<'SQL' +INSERT INTO `failed` (`id`, `id_report`, `test_name`, `signature`, `output`, `diff`) +VALUES (null, :id_report, :test_name, :signature, :output, :diff) +SQL; + $stmt = $dbi->prepare($query); + $stmt->bindValue(':id_report', $reportId, SQLITE3_INTEGER); + $stmt->bindValue(':test_name', $name, SQLITE3_TEXT); + $stmt->bindValue(':signature', md5($name.'__'.$test['diff'], true), SQLITE3_BLOB); + $stmt->bindValue(':output', $test['output'], SQLITE3_TEXT); + $stmt->bindValue(':diff', $test['diff'], SQLITE3_TEXT); + @$stmt->execute(); if ($dbi->lastErrorCode() != '') { echo "ERROR when inserting failed test : ".$dbi->lastErrorMsg()."\n"; exit; @@ -146,10 +159,14 @@ function insertToDb_phpmaketest($array, $QA_RELEASES = array()) } foreach ($array['expectedFailedTest'] as $name) { - $query = "INSERT INTO `expectedfail` - (`id`, `id_report`, `test_name`) VALUES (null, '".$reportId."', '".$name."')"; - - @$dbi->query($query); + $query = <<<'SQL' +INSERT INTO `expectedfail` (`id`, `id_report`, `test_name`) +VALUES (null, :id_report, :test_name) +SQL; + $stmt = $dbi->prepare($query); + $stmt->bindValue(':id_report', $reportId, SQLITE3_INTEGER); + $stmt->bindValue(':test_name', $name, SQLITE3_TEXT); + @$stmt->execute(); if ($dbi->lastErrorCode() != '') { echo "ERROR when inserting expected fail test : ".$dbi->lastErrorMsg()."\n"; exit; @@ -158,16 +175,23 @@ function insertToDb_phpmaketest($array, $QA_RELEASES = array()) foreach ($array['succeededTest'] as $name) { // sqlite files too big .. For the moment, keep only one success over time - $res = $dbi->query('SELECT id, id_report FROM `success` WHERE test_name LIKE \''. - $dbi->escapeString($name).'\''); + $query = 'SELECT id, id_report FROM `success` WHERE test_name LIKE :name'; + $stmt = $dbi->prepare($query); + $stmt->bindValue(':name', $name, SQLITE3_TEXT); + $res = $stmt->execute(); if ($res->numColumns() > 0) { // hit ! do nothing atm } else { - $query = "INSERT INTO `success` (`id`, `id_report`, `test_name`) - VALUES (null, '".$reportId."', '".$dbi->escapeString($name)."')"; + $query = <<<'SQL' +INSERT INTO `success` (`id`, `id_report`, `test_name`) +VALUES (null, :id_report, :test_name) +SQL; + $stmt = $dbi->prepare($query); + $stmt->bindValue(':id_report', $reportId, SQLITE3_INTEGER); + $stmt->bindValue(':test_name', $name, SQLITE3_TEXT); - @$dbi->query($query); + @$stmt->execute(); if ($dbi->lastErrorCode() != '') { echo "ERROR when inserting succeeded test : ".$dbi->lastErrorMsg()."\n"; exit; diff --git a/reports/run_tests.php b/reports/run_tests.php index 1b80a47..d611063 100644 --- a/reports/run_tests.php +++ b/reports/run_tests.php @@ -54,8 +54,10 @@ if (isset($_GET['version'])) { if (isset($_GET['expect']) && $_GET['expect'] == 1) { $query = 'SELECT \'xfail\' as xfail, test_name,COUNT(expectedfail.id) as cpt,\'-\' as variations, datetime(date) as date FROM expectedfail,reports WHERE expectedfail.id_report = reports.id - GROUP BY test_name ORDER BY cpt DESC LIMIT ' . $limit; - $q = @$database->query($query); + GROUP BY test_name ORDER BY cpt DESC LIMIT :limit'; + $stmt = $database->prepare($query); + $stmt->bindValue(':limit', $limit, SQLITE3_INTEGER); + $q = @$stmt->execute(); if ($q) { while ($tab = $q->fetchArray(SQLITE3_ASSOC)) { $failedTestsArray[] = $tab; @@ -69,8 +71,10 @@ if (isset($_GET['version'])) { LEFT JOIN failed f2 ON (f2.test_name=failed.test_name AND f2.output = "") LEFT JOIN reports r2 ON (f2.id_report = r2.id AND r2.user_email="ciqa") WHERE failed.id_report = reports.id - GROUP BY failed.test_name ORDER BY cpt DESC LIMIT ' . $limit; - $q = @$database->query($query); + GROUP BY failed.test_name ORDER BY cpt DESC LIMIT :limit'; + $stmt = $database->prepare($query); + $stmt->bindValue(':limit', $limit, SQLITE3_INTEGER); + $q = @$stmt->execute(); if (!$q) die("Error querying DB (error ".$database->lastErrorCode()."): ".$database->lastErrorMsg()); while ($tab = $q->fetchArray(SQLITE3_ASSOC)) { $failedTestsArray[] = $tab; diff --git a/reports/viewreports.php b/reports/viewreports.php index cde827b..f128514 100644 --- a/reports/viewreports.php +++ b/reports/viewreports.php @@ -42,10 +42,11 @@ if (!$database) { // GET infos from DB $query = 'SELECT id,signature, COUNT(*) as cpt, output, diff FROM failed -WHERE test_name="'.$database->escapeString($testName).'" +WHERE test_name=:test_name GROUP BY diff ORDER BY COUNT(*) desc'; - -$q = $database->query($query); +$stmt = $database->prepare($query); +$stmt->bindValue(':test_name', $testName, SQLITE3_TEXT); +$q = $stmt->execute(); $allDiffArray = array(); $sumCount = 0; while ($tab = $q->fetchArray(SQLITE3_ASSOC)) {