#[cfg(feature = "postgres")]
#[cfg(debug_assertions)]
mod postgres {
use concatsql::prelude::*;
use concatsql::prep;
use concatsql::{Error, ErrorLevel};
macro_rules! err {
() => { Err(Error::AnyError) };
($msg:expr) => { Err(Error::Message($msg.to_string())) };
}
pub fn prepare() -> concatsql::Connection {
let conn = concatsql::postgres::open("postgresql://postgres:postgres@localhost").unwrap();
conn.error_level(ErrorLevel::Debug);
let stmt = prep!(stmt());
conn.execute(stmt).unwrap();
conn
}
fn stmt() -> &'static str {
r#"CREATE TEMPORARY TABLE users (name TEXT, age INTEGER);
INSERT INTO users (name, age) VALUES ('Alice', 42);
INSERT INTO users (name, age) VALUES ('Bob', 69);
INSERT INTO users (name, age) VALUES ('Carol', 50);"#
}
#[test]
fn open() {
let _conn = concatsql::postgres::open("host=localhost user=postgres password=postgres").unwrap();
let _conn = concatsql::postgres::open("postgresql://postgres:postgres@localhost").unwrap();
}
#[test]
fn execute() {
let conn = concatsql::postgres::open("postgresql://postgres:postgres@localhost").unwrap();
let stmt = prep!(stmt());
conn.execute(stmt).unwrap();
}
#[test]
fn iterate() {
let conn = prepare();
let expects = ["Alice", "Bob", "Carol"];
let sql = prep!("SELECT name FROM users;");
let mut i = 0;
conn.iterate(sql, |pairs| {
for (_, value) in pairs {
assert_eq!(*value.as_ref().unwrap(), expects[i]);
i += 1;
}
true
}).unwrap();
}
#[test]
#[should_panic = "exec error"]
fn multiple_stm_should_errort() {
let conn = prepare();
let sql = prep!("SELECT name FROM users; SELECT name FROM users;");
conn.iterate(sql, |_| { unreachable!(); }).unwrap();
}
#[test]
fn iterate_or() {
let conn = prepare();
let expects = ["Alice", "Bob"];
let age = 50; // "50" error
let sql = prep!("SELECT name FROM users WHERE ") +
&prep!("age < ") + age + &prep!(" OR ") + age + &prep!(" < age");
let mut i = 0;
conn.iterate(sql, |pairs| {
for (_, value) in pairs {
assert_eq!(*value.as_ref().unwrap(), expects[i]);
i += 1;
}
true
}).unwrap();
}
#[test]
fn rows() {
let conn = prepare();
let expects = [("Alice", 42), ("Bob", 69), ("Carol", 50)];
let sql = prep!("SELECT * FROM users;");
let mut cnt = 0;
let rows = conn.rows(&sql).unwrap();
for (i, row) in rows.iter().enumerate() {
cnt += 1;
assert_eq!(row.get("name").unwrap(), expects[i].0);
assert_eq!(row.get("age").unwrap(), expects[i].1.to_string());
}
assert!(cnt == expects.len());
}
#[test]
fn rows_foreach() {
let conn = prepare();
let expects = [("Alice", 42), ("Bob", 69), ("Carol", 50)];
let mut cnt = 0;
conn.rows(&prep!("SELECT * FROM users;")).unwrap().iter().enumerate().for_each(|(i, row)| {
cnt += 1;
assert_eq!(row.get("name").unwrap(), expects[i].0);
assert_eq!(row.get("age").unwrap(), expects[i].1.to_string());
});
assert!(cnt == expects.len());
}
#[test]
fn start_with_quotation_and_end_with_anything_else() {
let conn = prepare();
let name = "'Alice'; DROP TABLE users; --";
let sql = prep!("select age from users where name = ") + name + &prep!("");
assert_eq!(
sql.simulate(),
"select age from users where name = '''Alice''; DROP TABLE users; --'"
);
conn.iterate(&sql, |_| { unreachable!(); }).unwrap();
}
#[test]
fn whitespace() {
let conn = prepare();
let sql = prep!("select\n*\rfrom\nusers;");
conn.iterate(sql, |_| { true }).unwrap();
}
#[test]
fn sqli_eq_nonquote() {
let conn = prepare();
let name = "Alice' or '1'='1";
let sql = prep!("select age from users where name =") + name + &prep!(";");
// "select age from users where name = 'Alice'' or ''1''=''1';"
conn.iterate(sql, |_| { unreachable!(); }).unwrap();
}
#[test]
fn sanitizing() {
let conn = prepare();
let name = r#""#;
let sql = prep!("INSERT INTO users VALUES(") + name + &prep!(", 12345);");
conn.execute(sql).unwrap();
conn.rows(prep!("SELECT name FROM users WHERE age = 12345;")).unwrap().iter() .all(|row| {
assert_eq!(
concatsql::html_special_chars(row.get("name").unwrap()),
"<script>alert("&1");</script>"
);
true
});
}
#[test]
fn error_level() {
let conn = concatsql::postgres::open("postgresql://postgres:postgres@localhost").unwrap();
conn.error_level(ErrorLevel::AlwaysOk);
conn.error_level(ErrorLevel::Release);
conn.error_level(ErrorLevel::Develop);
conn.error_level(ErrorLevel::Debug);
}
#[test]
#[allow(non_snake_case)]
fn error_level_AlwaysOk() {
let conn = concatsql::postgres::open("postgresql://postgres:postgres@localhost").unwrap();
conn.error_level(ErrorLevel::AlwaysOk);
let invalid_sql = "INVALID_SQL";
assert_eq!(conn.execute(invalid_sql), Ok(()));
assert_eq!(conn.iterate(invalid_sql, |_| unreachable!()), Ok(()));
assert_eq!(conn.rows(invalid_sql), Ok(Vec::new()));
}
#[test]
fn error_level_release() {
let conn = concatsql::postgres::open("postgresql://postgres:postgres@localhost").unwrap();
conn.error_level(ErrorLevel::Release);
let invalid_sql = "INVALID_SQL";
assert_eq!(conn.execute(invalid_sql), err!());
assert_eq!(conn.iterate(invalid_sql, |_| unreachable!()), err!());
assert_eq!(conn.rows(invalid_sql), err!());
}
#[test]
fn error_level_develop() {
let conn = concatsql::postgres::open("postgresql://postgres:postgres@localhost").unwrap();
conn.error_level(ErrorLevel::Develop);
let invalid_sql = "INVALID_SQL";
assert_eq!(conn.execute(invalid_sql), err!("exec error"));
assert_eq!(conn.iterate(invalid_sql, |_| unreachable!()), err!("exec error"));
assert_eq!(conn.rows(invalid_sql), err!("exec error"));
}
#[test]
fn error_level_debug() {
let conn = concatsql::postgres::open("postgresql://postgres:postgres@localhost").unwrap();
conn.error_level(ErrorLevel::Debug);
let invalid_sql = "INVALID_SQL";
assert_eq!(conn.execute(invalid_sql),
err!("exec error: db error: ERROR: \"INVALID_SQL\"またはその近辺で構文エラー"));
assert_eq!(conn.iterate(invalid_sql, |_| unreachable!()),
err!("exec error: db error: ERROR: \"INVALID_SQL\"またはその近辺で構文エラー"));
assert_eq!(conn.rows(invalid_sql),
err!("exec error: db error: ERROR: \"INVALID_SQL\"またはその近辺で構文エラー"));
}
#[test]
fn integer() {
let conn = prepare();
let age = 50;
let sql = prep!("select name from users where age < ") + age;
for row in conn.rows(&sql).unwrap() {
assert_eq!(row.get("name").unwrap(), "Alice");
}
}
#[test]
fn prep_into_execute() {
let conn = concatsql::postgres::open("postgresql://postgres:postgres@localhost").unwrap();
conn.execute(prep!("SELECT ") + 1 + prep!("::INTEGER")).unwrap();
}
#[test]
fn prep_into_iterate() {
let conn = concatsql::postgres::open("postgresql://postgres:postgres@localhost").unwrap();
conn.iterate(prep!("SELECT ") + 1 + prep!("::INTEGER"), |_| true ).unwrap();
}
#[test]
fn prep_into_rows() {
let conn = concatsql::postgres::open("postgresql://postgres:postgres@localhost").unwrap();
for row in conn.rows(prep!("SELECT ") + 1 + prep!("::INTEGER")).unwrap() {
assert_eq!(row.column_name(0).unwrap(), "int4");
assert_eq!(row.get("int4").unwrap(), "1");
assert_eq!(row.get(0).unwrap(), "1");
}
for row in conn.rows(prep!("SELECT ") + "1" + prep!("::TEXT")).unwrap() {
assert_eq!(row.column_name(0).unwrap(), "text");
assert_eq!(row.get("text").unwrap(), "1");
assert_eq!(row.get(0).unwrap(), "1");
}
for row in conn.rows(prep!("SELECT 1")).unwrap() {
assert_eq!(row.column_name(0).unwrap(), "?column?");
assert_eq!(row.get("?column?").unwrap(), "1");
assert_eq!(row.get(0).unwrap(), "1");
}
}
#[test]
fn like() {
let conn = prepare();
let name = "A%";
let sql = prep!("SELECT * FROM users WHERE name LIKE ") + name;
let mut executed = false;
conn.rows(&sql).unwrap().iter().all(|row| {
assert_eq!(row.get("name").unwrap(), "Alice");
executed = true;
true
});
assert!(executed);
let name = "A";
let sql = prep!("SELECT * FROM users WHERE name LIKE ") + ("%".to_owned() + name + "%");
assert_eq!(sql.simulate(), "SELECT * FROM users WHERE name LIKE '%A%'");
conn.execute(&sql).unwrap();
let name = "%A%";
let sql = prep!("SELECT * FROM users WHERE name LIKE ") + ("%".to_owned() + &sanitize_like!(name) + "%");
assert_eq!(sql.simulate(), "SELECT * FROM users WHERE name LIKE '%\\\\%A\\\\%%'");
conn.execute(&sql).unwrap();
let name = String::from("%A%");
let sql = prep!("SELECT * FROM users WHERE name LIKE ") + ("%".to_owned() + &sanitize_like!(name, '$') + "%");
assert_eq!(sql.simulate(), "SELECT * FROM users WHERE name LIKE '%$%A$%%'");
conn.execute(&sql).unwrap();
}
#[test]
fn multiple_stmt() {
let conn = prepare();
let mut cnt = 0;
for (i, row) in conn.rows("SELECT 1 UNION SELECT 2;").unwrap().iter().enumerate() {
cnt += 1;
assert_eq!(row.get_into::<_, i32>(0).unwrap(), [ 1, 2 ][i]);
};
for (i, row) in conn.rows("SELECT age FROM users;").unwrap().iter().enumerate() {
cnt += 1;
assert_eq!(row.get_into::<_, i32>(0).unwrap(), [ 42, 69, 50 ][i]);
};
assert_eq!(cnt, 5);
}
#[test]
#[ignore]
fn mass_connection() {
let capacity = 64;
let mut conns = Vec::with_capacity(capacity);
for _ in 0..capacity {
conns.push(concatsql::postgres::open("postgresql://postgres:postgres@localhost").unwrap());
}
for i in 1..capacity {
assert_ne!(conns[0], conns[i]);
}
}
#[test]
fn blob() {
let conn = concatsql::postgres::open("postgresql://postgres:postgres@localhost").unwrap();
conn.execute("CREATE TEMPORARY TABLE b (data bytea)").unwrap();
let data = vec![0x1, 0xA, 0xFF, 0x00, 0x7F];
let sql = prep!("INSERT INTO b VALUES (") + &data + prep!(")");
conn.execute(sql).unwrap();
for row in conn.rows("SELECT data FROM b").unwrap() {
assert_eq!(row.get_into::<_, Vec>(0).unwrap(), data);
}
}
#[test]
fn question() {
let conn = prepare();
let sql = prep!("SELECT name FROM users WHERE name=") + "?";
for _ in conn.rows(&sql).unwrap() { unreachable!(); }
}
#[test]
fn map_collect() {
let conn = prepare();
let rows = conn.rows("SELECT * FROM users").unwrap();
let names = rows.iter().map(|row| row.get("name")).collect::>>();
let mut cnt = 0;
for (i, name) in names.iter().enumerate() {
cnt += 1;
assert_eq!(name.unwrap(), ["Alice","Bob","Carol"][i])
}
assert_eq!(cnt, 3);
}
#[test]
fn in_array() {
let conn = prepare();
let sql = prep!("SELECT * FROM users WHERE name IN (") + vec![] as Vec<&str> + prep!(")");
conn.rows(&sql).unwrap();
let sql = prep!("SELECT * FROM users WHERE name IN (") + vec!["Adam"] + prep!(")");
conn.rows(&sql).unwrap();
let sql = prep!("SELECT * FROM users WHERE name IN (") + vec!["Adam","Eve"] + prep!(")");
conn.rows(&sql).unwrap();
}
#[test]
fn uuid() {
use uuid::Uuid;
let conn = prepare();
let sql = prep!("SELECT ") + Uuid::nil();
for row in conn.rows(&sql).unwrap() {
assert_eq!(&row[0], "00000000000000000000000000000000");
}
let sql = prep!("SELECT ") + Uuid::parse_str("936DA01F-9ABD-4D9D-80C7-02AF85C822A8").unwrap();
for row in conn.rows(&sql).unwrap() {
assert_eq!(&row[0], "936DA01F9ABD4D9D80C702AF85C822A8");
}
}
#[test]
fn sql_injection() {
let conn = prepare();
let name = "'' OR 1=1; --";
let sql = prep!("SELECT age FROM users WHERE name = ") + name;
for _ in conn.rows(&sql).unwrap() {
unreachable!();
}
let name = "''; DROP TABLE users; --";
let sql = prep!("SELECT age FROM users WHERE name = ") + name;
for _ in conn.rows(&sql).unwrap() {
unreachable!();
}
let sql = prep!("SELECT ") + "0x50 + 0x45";
for row in conn.rows(&sql).unwrap() {
assert_eq!(row.get(0).unwrap(), "0x50 + 0x45");
}
let sql = prep!("SELECT ") + "0x414243";
for row in conn.rows(&sql).unwrap() {
assert_eq!(row.get(0).unwrap(), "0x414243");
}
let sql = prep!("SELECT ") + "CHAR(0x66)";
for row in conn.rows(&sql).unwrap() {
assert_eq!(row.get(0).unwrap(), "CHAR(0x66)");
}
let sql = prep!("SELECT ") + "IF(1=1, 'true', 'false')";
for row in conn.rows(&sql).unwrap() {
assert_eq!(row.get(0).unwrap(), "IF(1=1, 'true', 'false')");
}
let sql = prep!("SELECT ") + "na + '-' + me FROM users";
for row in conn.rows(&sql).unwrap() {
assert_eq!(row.get(0).unwrap(), "na + '-' + me FROM users");
}
let sql = prep!("SELECT ") + "ASCII('a')";
for row in conn.rows(&sql).unwrap() {
assert_eq!(row.get(0).unwrap(), "ASCII('a')");
}
let sql = prep!("SELECT ") + "CHAR(64)";
for row in conn.rows(&sql).unwrap() {
assert_eq!(row.get(0).unwrap(), "CHAR(64)");
}
}
}
#[cfg(feature = "postgres")]
mod anti_patterns {
use concatsql::prep;
// Although it becomes possible, I do not believe it is less useful
// because its real advantage is that it still makes it harder to do the wrong thing.
#[test]
fn string_to_static_str() {
let conn = concatsql::postgres::open("postgresql://postgres:postgres@localhost").unwrap();
let sql: &'static str = Box::leak(String::from("SELECT 1").into_boxed_str()); // Leak!
conn.execute(sql).unwrap();
}
#[test]
fn text_op_integer() {
let conn = super::postgres::prepare();
let sql = prep!("SELECT age FROM users WHERE name = ") + i32::MAX;
assert!(conn.rows(&sql).is_err());
let sql = prep!("SELECT age FROM users WHERE name < ") + i32::MAX;
assert!(conn.rows(&sql).is_err());
let sql = prep!("SELECT age FROM users WHERE name > ") + i32::MAX;
assert!(conn.rows(&sql).is_err());
let sql = prep!("SELECT age FROM users WHERE name = ") + i32::MIN;
assert!(conn.rows(&sql).is_err());
let sql = prep!("SELECT age FROM users WHERE name < ") + i32::MIN;
assert!(conn.rows(&sql).is_err());
let sql = prep!("SELECT age FROM users WHERE name > ") + i32::MIN;
assert!(conn.rows(&sql).is_err());
let sql = prep!("SELECT age FROM users WHERE name = ") + u32::MAX;
assert!(conn.rows(&sql).is_err());
let sql = prep!("SELECT age FROM users WHERE name < ") + u32::MAX;
assert!(conn.rows(&sql).is_err());
let sql = prep!("SELECT age FROM users WHERE name > ") + u32::MAX;
assert!(conn.rows(&sql).is_err());
let sql = prep!("SELECT age FROM users WHERE name = ") + u32::MIN;
assert!(conn.rows(&sql).is_err());
let sql = prep!("SELECT age FROM users WHERE name < ") + u32::MIN;
assert!(conn.rows(&sql).is_err());
let sql = prep!("SELECT age FROM users WHERE name > ") + u32::MIN;
assert!(conn.rows(&sql).is_err());
}
#[test]
#[should_panic = "expected 0 parameters but got 1"]
fn invalid_placeholders() {
let conn = super::postgres::prepare();
let name = "' OR 1=2; SELECT 1; --";
let sql = prep!("SELECT age FROM users WHERE name = '") + name + &prep!("';"); // '?' is not placeholder
conn.execute(sql).ok();
let name = "' OR 1=1; --";
let sql = prep!("SELECT age FROM users WHERE name = '") + name + &prep!("';"); // '?' is not placeholder
conn.execute(sql).ok();
let name = "Alice";
let sql = prep!("SELECT age FROM users WHERE name = '") + name + &prep!("';"); // '?' is not placeholder
conn.execute(sql).ok();
}
}