CS-CartのSQLプレースホルダの挙動 #cs-cart

CS-Cartには自前のSQLプレースホルダがあるが、その中で挙動が気になったものをまとめる。プレースホルダの概要については本家ドキュメント『Using Placeholders to Build Database Requests — CS-Cart 4.0.x documentation』を参照。

DB接頭辞 `?:`

db_query('SELECT * FROM ?:users');

結果

SELECT * FROM cscart_users

Integer `?i`

Integerといいつつ、doubleはそのまま通ってしまうので注意。

db_query("SELECT * FROM ?:users WHERE user_id = ?i", null); // NULL
db_query("SELECT * FROM ?:users WHERE user_id = ?i", true); // boolean
db_query("SELECT * FROM ?:users WHERE user_id = ?i", false); // boolean
db_query("SELECT * FROM ?:users WHERE user_id = ?i", 1); // integer
db_query("SELECT * FROM ?:users WHERE user_id = ?i", 1.5); // double
db_query("SELECT * FROM ?:users WHERE user_id = ?i", "12"); // string
db_query("SELECT * FROM ?:users WHERE user_id = ?i", "12ABC"); // string
db_query("SELECT * FROM ?:users WHERE user_id = ?i", "A"); // string

結果

SELECT * FROM cscart_users WHERE user_id = 0 -- null
SELECT * FROM cscart_users WHERE user_id = 1 -- true
SELECT * FROM cscart_users WHERE user_id = 0 -- false
SELECT * FROM cscart_users WHERE user_id = 1 -- 1
SELECT * FROM cscart_users WHERE user_id = 1.5 -- 1.5
SELECT * FROM cscart_users WHERE user_id = 12 -- "12"
SELECT * FROM cscart_users WHERE user_id = 12 -- "12ABC"
SELECT * FROM cscart_users WHERE user_id = 0 -- "A"

内部的には+ 0しているだけ

    /**
     * Convert variable to int/longint type
     *
     * @param  mixed $int variable to convert
     * @return mixed int/intval variable
     */
    protected static function intVal($int)
    {
        return $int + 0;
    }

String `?s`

クォーテーションをエスケープしてくれる。

db_query("SELECT * FROM ?:users WHERE email = ?s", null); // NULL
db_query("SELECT * FROM ?:users WHERE email = ?s", true); // boolean
db_query("SELECT * FROM ?:users WHERE email = ?s", false); // boolean
db_query("SELECT * FROM ?:users WHERE email = ?s", 1); // integer
db_query("SELECT * FROM ?:users WHERE email = ?s", 1.5); // double
db_query("SELECT * FROM ?:users WHERE email = ?s", "12"); // string
db_query("SELECT * FROM ?:users WHERE email = ?s", "'single quotes'"); // string
db_query("SELECT * FROM ?:users WHERE email = ?s", "\"double quotes\""); // string
db_query("SELECT * FROM ?:users WHERE email = ?s", "%partial match%"); // string
db_query("SELECT * FROM ?:users WHERE email = ?s", "back \\ slash"); // string
db_query("SELECT * FROM ?:users WHERE email = ?s", "_one char"); // string

結果

SELECT * FROM cscart_users WHERE email = '' -- null
SELECT * FROM cscart_users WHERE email = '1' -- true
SELECT * FROM cscart_users WHERE email = '' -- false
SELECT * FROM cscart_users WHERE email = '1' -- 1
SELECT * FROM cscart_users WHERE email = '1.5' -- 1.5
SELECT * FROM cscart_users WHERE email = '12' -- "12"
SELECT * FROM cscart_users WHERE email = '\'single quotes\'' -- "'single quotes'"
SELECT * FROM cscart_users WHERE email = '\"double quotes\"' -- "\"double quotes\""
SELECT * FROM cscart_users WHERE email = '%partial match%' -- "%partial match%"
SELECT * FROM cscart_users WHERE email = 'back \\ slash' -- "back \\ slash"
SELECT * FROM cscart_users WHERE email = '_one char' -- "_one char"

LIKE用String `?l`

MySQLのLIKEではバックスラッシュを二重しないといけないが、これを面倒見てくれる。

db_query("SELECT * FROM ?:users WHERE email LIKE ?l", null); // NULL
db_query("SELECT * FROM ?:users WHERE email LIKE ?l", true); // boolean
db_query("SELECT * FROM ?:users WHERE email LIKE ?l", false); // boolean
db_query("SELECT * FROM ?:users WHERE email LIKE ?l", 1); // integer
db_query("SELECT * FROM ?:users WHERE email LIKE ?l", 1.5); // double
db_query("SELECT * FROM ?:users WHERE email LIKE ?l", "12"); // string
db_query("SELECT * FROM ?:users WHERE email LIKE ?l", "'single quotes'"); // string
db_query("SELECT * FROM ?:users WHERE email LIKE ?l", "\"double quotes\""); // string
db_query("SELECT * FROM ?:users WHERE email LIKE ?l", "%partial match%"); // string
db_query("SELECT * FROM ?:users WHERE email LIKE ?l", "back \\ slash"); // string
db_query("SELECT * FROM ?:users WHERE email LIKE ?l", "_one char"); // string

結果

SELECT * FROM cscart_users WHERE email LIKE '' -- null
SELECT * FROM cscart_users WHERE email LIKE '1' -- true
SELECT * FROM cscart_users WHERE email LIKE '' -- false
SELECT * FROM cscart_users WHERE email LIKE '1' -- 1
SELECT * FROM cscart_users WHERE email LIKE '1.5' -- 1.5
SELECT * FROM cscart_users WHERE email LIKE '12' -- "12"
SELECT * FROM cscart_users WHERE email LIKE '\'single quotes\'' -- "'single quotes'"
SELECT * FROM cscart_users WHERE email LIKE '\"double quotes\"' -- "\"double quotes\""
SELECT * FROM cscart_users WHERE email LIKE '%partial match%' -- "%partial match%"
SELECT * FROM cscart_users WHERE email LIKE 'back \\\\ slash' -- "back \\ slash"
SELECT * FROM cscart_users WHERE email LIKE '_one char' -- "_one char"

Double `?d`

小数点第二位で丸める。見た感じ五捨六入？

db_query("SELECT ?d", null); // NULL
db_query("SELECT ?d", true); // boolean
db_query("SELECT ?d", false); // boolean
db_query("SELECT ?d", 1); // integer
db_query("SELECT ?d", 1.5); // double
db_query("SELECT ?d", 1.115); // double
db_query("SELECT ?d", 1.116); // double
db_query("SELECT ?d", "1"); // string
db_query("SELECT ?d", "1.5"); // string
db_query("SELECT ?d", "1.115"); // string
db_query("SELECT ?d", "1.116"); // string
db_query("SELECT ?d", "A"); // string
db_query("SELECT ?d", "100A"); // string

結果

SELECT 0.00 -- null
SELECT 1.00 -- true
SELECT 0.00 -- false
SELECT 1.00 -- 1
SELECT 1.50 -- 1.5
SELECT 1.11 -- 1.115
SELECT 1.12 -- 1.116
SELECT 1.00 -- "1"
SELECT 1.50 -- "1.5"
SELECT 1.11 -- "1.115"
SELECT 1.12 -- "1.116"
SELECT 0.00 -- "A"
SELECT 100.00 -- "100A"

Array(IN句用) `?a`

Arrayだけでなくスカラー型も受け付ける。空の配列が渡ってくると、Deprecatedエラーが出るがSQLは実行される。

db_query("SELECT * FROM ?:users WHERE user_id IN (?a)", null); // NULL
db_query("SELECT * FROM ?:users WHERE user_id IN (?a)", true); // boolean
db_query("SELECT * FROM ?:users WHERE user_id IN (?a)", false); // boolean
db_query("SELECT * FROM ?:users WHERE user_id IN (?a)", 1); // integer
db_query("SELECT * FROM ?:users WHERE user_id IN (?a)", 1.5); // double
db_query("SELECT * FROM ?:users WHERE user_id IN (?a)", "1"); // string
db_query("SELECT * FROM ?:users WHERE user_id IN (?a)", "0755"); // string
db_query("SELECT * FROM ?:users WHERE user_id IN (?a)", "1A"); // string
db_query("SELECT * FROM ?:users WHERE user_id IN (?a)", []); // array
db_query("SELECT * FROM ?:users WHERE user_id IN (?a)", [null,true,false]); // array
db_query("SELECT * FROM ?:users WHERE user_id IN (?a)", [1,1.5,"1","0755","1A"]); // array
db_query("SELECT * FROM ?:users WHERE user_id IN (?a)", ["'single quotes'","\"double quotes\""]); // array

結果

SELECT * FROM cscart_users WHERE user_id IN (NULL) -- null
SELECT * FROM cscart_users WHERE user_id IN ('1') -- true
SELECT * FROM cscart_users WHERE user_id IN ('') -- false
SELECT * FROM cscart_users WHERE user_id IN (1) -- 1
SELECT * FROM cscart_users WHERE user_id IN (1.5) -- 1.5
SELECT * FROM cscart_users WHERE user_id IN (1) -- "1"
SELECT * FROM cscart_users WHERE user_id IN ('0755') -- "0755"
SELECT * FROM cscart_users WHERE user_id IN ('1A') -- "1A"
SELECT * FROM cscart_users WHERE user_id IN (NULL) -- []
Deprecated: Empty array was passed into SQL statement IN() in /var/www/html/app/Tygh/Database.php on line 472

SELECT * FROM cscart_users WHERE user_id IN (NULL, '1', '') -- [null,true,false]
SELECT * FROM cscart_users WHERE user_id IN (1, 1.5, 1, '0755', '1A') -- [1,1.5,"1","0755","1A"]
SELECT * FROM cscart_users WHERE user_id IN ('\'single quotes\'', '\"double quotes\"') -- ["'single quotes'","\"double quotes\""]

Numbers (IN句用) `?n`

?aと同じくIN句用のプレースホルダだが、数値の配列に変換される。

db_query("SELECT * FROM ?:users WHERE user_id IN (?n)", null); // NULL
db_query("SELECT * FROM ?:users WHERE user_id IN (?n)", true); // boolean
db_query("SELECT * FROM ?:users WHERE user_id IN (?n)", false); // boolean
db_query("SELECT * FROM ?:users WHERE user_id IN (?n)", 1); // integer
db_query("SELECT * FROM ?:users WHERE user_id IN (?n)", 1.5); // double
db_query("SELECT * FROM ?:users WHERE user_id IN (?n)", "1"); // string
db_query("SELECT * FROM ?:users WHERE user_id IN (?n)", "0755"); // string
db_query("SELECT * FROM ?:users WHERE user_id IN (?n)", "1A"); // string
db_query("SELECT * FROM ?:users WHERE user_id IN (?n)", []); // array
db_query("SELECT * FROM ?:users WHERE user_id IN (?n)", [null,true,false]); // array
db_query("SELECT * FROM ?:users WHERE user_id IN (?n)", [1,1.5,"1","0755","1A"]); // array
db_query("SELECT * FROM ?:users WHERE user_id IN (?n)", ["'single quotes'","\"double quotes\""]); // array

結果

SELECT * FROM cscart_users WHERE user_id IN (0) -- null
SELECT * FROM cscart_users WHERE user_id IN (1) -- true
SELECT * FROM cscart_users WHERE user_id IN (0) -- false
SELECT * FROM cscart_users WHERE user_id IN (1) -- 1
SELECT * FROM cscart_users WHERE user_id IN (1.5) -- 1.5
SELECT * FROM cscart_users WHERE user_id IN (1) -- "1"
SELECT * FROM cscart_users WHERE user_id IN (755) -- "0755"
SELECT * FROM cscart_users WHERE user_id IN (1) -- "1A"
SELECT * FROM cscart_users WHERE user_id IN ('') -- []
SELECT * FROM cscart_users WHERE user_id IN (0, 1, 0) -- [null,true,false]
SELECT * FROM cscart_users WHERE user_id IN (1, 1.5, 1, 755, 1) -- [1,1.5,"1","0755","1A"]
SELECT * FROM cscart_users WHERE user_id IN (0, 0) -- ["'single quotes'","\"double quotes\""]

CS-CartのSQLプレースホルダの挙動

DB接頭辞 ?:

Integer ?i

String ?s

LIKE用String ?l

Double ?d

Array(IN句用) ?a

Numbers (IN句用) ?n