- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我有两个问题:
有没有更简洁的方法让我执行这个准备好的语句?似乎这里发生了很多事情。
您能否解决代码中“我如何清理它”的部分?我想确保它的安全。
顺便说一句,这一切似乎工作得很好。只是想稍微修复一下。
<?php
session_start();
require_once './config/config.php';
require_once 'includes/auth_validate.php';
include_once 'includes/header.php';
error_reporting (E_ALL ^ E_NOTICE);
//ONLY SUPER AND ADMINS ARE ALLOWED TO ACCESS THIS PAGE
if ($_SESSION['admin_type'] !=='admin' && $_SESSION['admin_type'] !=='super') {
$_SESSION['admin_type'] = "undefined";
echo 'Permission Denied';
exit();
}
// How can I sanatize this
$customer_id = filter_input(INPUT_GET, 'customer_id', FILTER_VALIDATE_INT);
$operation = filter_input(INPUT_GET, 'operation',FILTER_SANITIZE_STRING);
($operation == 'edit') ? $edit = true : $edit = false;
$db = getDbInstance();
$cid = htmlentities ($_GET['customer_id']);
$sql = "SELECT
(SELECT treatment_log.bdi FROM treatment_log LEFT JOIN customers ON treatment_log.treatment_fk=customers.id WHERE treatment_log.created_at=(SELECT MIN(created_at) FROM treatment_log WHERE treatment_fk = ?) AND customers.id = ?) AS first_bdi,
(SELECT treatment_log.pain FROM treatment_log LEFT JOIN customers ON treatment_log.treatment_fk=customers.id WHERE treatment_log.created_at=(SELECT MIN(created_at) FROM treatment_log WHERE treatment_fk = ?) AND customers.id = ?) AS first_pain,
(SELECT treatment_log.suicidality FROM treatment_log LEFT JOIN customers ON treatment_log.treatment_fk=customers.id WHERE treatment_log.created_at=(SELECT MIN(created_at) FROM treatment_log WHERE treatment_fk = ?) AND customers.id = ?) AS first_suicidality,
(SELECT treatment_log.bdi FROM treatment_log LEFT JOIN customers ON treatment_log.treatment_fk=customers.id WHERE treatment_log.created_at=(SELECT MAX(created_at) FROM treatment_log WHERE treatment_fk = ?) AND customers.id = ?) AS last_bdi,
(SELECT treatment_log.pain FROM treatment_log LEFT JOIN customers ON treatment_log.treatment_fk=customers.id WHERE treatment_log.created_at=(SELECT MAX(created_at) FROM treatment_log WHERE treatment_fk = ?) AND customers.id = ?) AS last_pain,
(SELECT treatment_log.suicidality FROM treatment_log LEFT JOIN customers ON treatment_log.treatment_fk=customers.id WHERE treatment_log.created_at=(SELECT MAX(created_at) FROM treatment_log WHERE treatment_fk = ?) AND customers.id = ?) AS last_suicidality,
(SELECT CAST(treatment_log.created_at as DATE) FROM treatment_log LEFT JOIN customers ON treatment_log.treatment_fk = customers.id WHERE treatment_log.created_at = (SELECT MAX(created_at) FROM treatment_log WHERE treatment_fk = ?)AND customers.id = ?) AS last_visit,
(SELECT COUNT(*) FROM treatment_log WHERE treatment_fk = ?) AS completed_treatments,
(SELECT COUNT(*) FROM treatment_log WHERE treatment_fk = ? AND missed_treatment='yes') AS number_of_missed_treatments,
(SELECT COUNT(*) FROM bdi WHERE bdi_fk = ?) AS completed_bdis,
(SELECT customers.f_name FROM customers WHERE customers.id = ?) AS f_name,
(SELECT customers.l_name FROM customers WHERE customers.id = ?) AS l_name,
(SELECT customers.status FROM customers WHERE customers.id = ?) AS status,
(SELECT customers.mrn FROM customers WHERE customers.id = ?) AS mrn,
(SELECT customers.ohip FROM customers WHERE customers.id = ?) AS ohip,
(SELECT customers.sex FROM customers WHERE customers.id = ?) AS sex,
(SELECT customers.address FROM customers WHERE customers.id = ?) AS address,
(SELECT customers.city FROM customers WHERE customers.id = ?) AS city,
(SELECT customers.postal_code FROM customers WHERE customers.id = ?) AS country,
(SELECT customers.phone FROM customers WHERE customers.id = ?) AS phone,
(SELECT customers.about FROM customers WHERE customers.id = ?) AS about,
(SELECT customers.date_of_birth FROM customers WHERE customers.id = ?) AS date_of_birth,
(SELECT customers.protocol FROM customers WHERE customers.id = ?) AS protocol,
(SELECT customers.treatment_location FROM customers WHERE customers.id = ?) AS treatment_location,
(SELECT customers.area FROM customers WHERE customers.id = ?) AS area,
(SELECT customers.dx FROM customers WHERE customers.id = ?) AS dx,
(SELECT customers.room FROM customers WHERE customers.id = ?) AS room,
(SELECT customers.coil FROM customers WHERE customers.id = ?) AS coil,
(SELECT customers.target_threshold FROM customers WHERE customers.id = ?) AS target_thresold,
(SELECT customers.number_of_treatments FROM customers WHERE customers.id = ?) AS number_of_treatments,
(SELECT customers.motor_threshold FROM customers WHERE customers.id = ?) AS motor_threshold,
(SELECT customers.threshold_multiplier FROM customers WHERE customers.id = ?) AS threshold_multiplier,
(SELECT customers.created_at FROM customers WHERE customers.id = ?) AS created_at,
(SELECT customers.updated_at FROM customers WHERE customers.id = ?) AS updated_at;";
$stmt = mysqli_stmt_init($conn);
mysqli_stmt_prepare($stmt, $sql);
mysqli_stmt_bind_param($stmt, "iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii", $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid, $cid);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
?>
最佳答案
如评论所述,htmlentities()
与防止 SQL 注入(inject)无关。当你想输出一些内容到 HTML,并且你想避免 XSS 漏洞时使用它。
我不会将所有这些查询都写成单独的子查询。您不太可能需要通过单个 SQL 语句获取所有信息。试图将如此多的工作塞进单个 SQL 调用并不能显着提高效率,但它确实使您编写代码变得更加困难。
记住这个智慧:
Everyone knows that debugging is twice as hard as writing a program in the first place. So if you're as clever as you can be when you write it, how will you ever debug it?
下面是我将如何编写代码:
如果您使用参数化查询,则完全没有必要进行清理。但如果您愿意,这里有一种更简单的方法:
$customer_id = (int) $_GET['customer_id'];
是的!只需转换为 (int)
。编写此代码很简单,在代码审查中也很容易解释,而且执行速度比函数调用快。
然后将 SQL 分成几个查询,以合理的方式将它们分组,这样您就不需要子查询或列别名。更简单的查询更容易编写代码、更容易调试、更容易修改(如果您以后需要这样做)(或者如果其他开发人员需要修改它,他们会感谢您编写更易于处理的代码)。
获得首次访问的一个简单方法是按 created_at 排序并使用 LIMIT 1。
$sql = "SELECT bdi, pain, suicidality FROM treatment_log WHERE treatment_fk = ?
ORDER BY created_at LIMIT 1";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $customer_id);
$stmt->execute();
$rows1 = $stmt->fetch_all(MYSQLI_ASSOC);
获取上次访问 - 按 created_at 降序 排序并使用 LIMIT 1。
$sql = "SELECT bdi, pain, suicidality, DATE(created_at) AS last_visit
FROM treatment_log WHERE treatment_fk = ?
ORDER BY created_at DESC LIMIT 1";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $customer_id);
$stmt->execute();
$rows2 = $stmt->fetch_all(MYSQLI_ASSOC);
这是一个技巧:在 MySQL 中,true 为 1,false 为 0,因此您可以SUM()
1 作为计算某些表达式为 true 的行数的方法。
$sql = "SELECT COUNT(*) AS completed_treatments,
SUM(missed_treatments='yes') AS number_of_missed_treatments
FROM treatment_log WHERE treatment_fk = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $customer_id);
$stmt->execute();
$rows3 = $stmt->fetch_all(MYSQLI_ASSOC);
其他查询非常简单。
$sql = "SELECT COUNT(*) AS completed_bdis FROM bdi WHERE bdi_fk = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $customer_id);
$stmt->execute();
$rows4 = $stmt->fetch_all(MYSQLI_ASSOC);
$sql = "SELECT f_name, l_name, status, mrn, ohip, sex, address, city, country,
phone, about, date_of_birth, protocol, treatment_location, area, dx, room,
coil, target_threshold, number_of_treatments, motor_threshold,
threshold_multiplier, created_at, updated_at
FROM customers WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $customer_id);
$stmt->execute();
$rows5 = $stmt->fetch_all(MYSQLI_ASSOC);
在所有这些查询中,我们只需要为每个查询绑定(bind)一次$customer_id
,这使得编写这段代码变得更加容易。无需让自己眼睛疲劳地计算长“iiii...”字符串的长度并将其与参数数量相匹配。
P.S.:我没有测试这段代码,所以如果有错别字,我相信你能解决。
关于php - 修复准备好的声明,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/54174501/
我将一个 div 设置为 100% 宽度,当以 1024 分辨率查看页面时,宽度应从 100% 变为 1000px,我让它与@media 查询一起正常工作,并且在 FF、safari chrome 上
希望有人能帮助我,我已经被困了几天了。 将我的 Domino 服务器更新到 9.01 Fix 3 后,我在 javascript 控制台上不断收到错误消息: TypeError: this.edito
我们正在使用一个基于RMI的java应用程序。当我们运行应用程序时,即使应用程序处于理想阶段,内存使用量仍然不断增加。我们主要使用Vector和散列图数据结构。如何最大限度地减少java内存使用/修复
概述 Internet Download Manager (IDM)是最流行的 Windows 下载管理器。如果你平时工作中使用过IDM,您会惊叹 IDM 下载文件的速度有多快。IDM
当我打开 brave 浏览器时,会打开一个窗口(如下所示)。它并没有真正干扰浏览器的处理。但令人担忧的是为什么这种情况一直发生...... Error On Opening Brave Browser
这是我今天在求职面试中被问到的一个问题: 看下面的代码: int n=20; for (int i =0; i
我不小心删除了/opt/local/bin/perl5.8.9 ,这似乎是 macports 编译的 perl 的主要二进制文件。 现在我有很多取决于 perl5 的端口,但不想卸载并重新安装所有端口
>>>flip fix (0 :: Int) (\a b -> putStrLn "abc") Output: "abc" 这是使用翻转修复的简化版本。 我在一些 YouTube 视频中看到了这种使用
这个问题已经有答案了: How can I fix 'android.os.NetworkOnMainThreadException'? (64 个回答) 已关闭 3 年前。 我在 Android 应
def main(): cash = float(input("How much money: ")) coins = 0 def changeCounter(n): whil
前一周我遇到了类似的问题,查询需要永远运行。在编写此查询时,我尝试应用从其他查询中学到的一些知识,但执行起来需要很长时间。 运行查询的两个单独部分时,每个部分需要 2 分钟才能完成,这是可以接受的,但
下午,我的 CSS 有问题。第三个下拉菜单放错了,我没有解决办法。 这是我想要的: 之前: http://i53.tinypic.com/2qu85z8.png 之后: http://i51.tiny
更新方法: override func tableView(_ tableView: UITableView, commit editingStyle: UITableViewCellEditingS
我知道这是一个很多人都遇到过的问题,但我不熟悉 Less 并且是 Bootstrap 的新手,我正在寻找一种全 CSS 解决方案来防止我的导航栏折叠到 768 像素以下:
在我的布局中,我创建了以下 jsfiddle 托管的可调整大小的粘性页脚。但是,在调整大小时它与内容重叠。有没有办法让它在所有浏览器上都能响应? http://jsfiddle.net/9aLc0mg
我想要实现的目标 racer-offset 是为了让用户可以设置图像可以以 px 为单位移动多远。偏移量管理偏移量。 Speed-racer 告诉我们图像在滚动过程中移动的速度。我的问题是它不会停止。
我有一个简单的自动换行函数,它接受一个长字符串作为输入,然后将该字符串分成更小的字符串,并将它们添加到一个数组中,以便稍后输出。现在最后一两个字没有输出。这是主要问题。但是,我还想改进功能。我知道这有
我试图在使用每个 slider 之前禁用“下一步”按钮,我不确定为什么在单击不再是 class="not-clicked"的同一个 slider 时取消禁用该按钮. JSFiddle: (这里看起来有
这个问题已经有答案了: What is a NullPointerException, and how do I fix it? (12 个回答) 已关闭 8 年前。 如何让程序输出所有信息? IT
On this page ,在“生活”下有一个带有自动生成的子菜单的菜单。子菜单存在一些问题(它会闪烁并改变大小——如果你滚动它就会看到)。我需要以某种方式覆盖它当前正在读取的 css 并使其统一。
我是一名优秀的程序员,十分优秀!