mysql (very) dynamic prepared statement

Question

Is there any possibility to making something similar like this example to working in mysql?

SET @my_columns = "( col_1 , col_2 , col_n )";
SET @my_values = "( val_1 , val_2 , val_n )";

SET @vs_query = "INSERT INTO my_table ? VALUES ? "; 

PREPARE stmt FROM @vs_query;
EXECUTE stmt USING @my_columns , @my_values;
DEALLOCATE PREPARE stmt;

@ Strawberry I think due to my lack of experience, you're right. But I do not understand because if I have, for example, a lot of calls to a database where I manually specify all columns + values, it is considered a good design, however if I am going to change the structure of the database, I will have Change all values in my (php code for example) This = efficiency zero or close to zero; Is that a good design? (In the real wold the thinks are not inamovibles dynamic = dynamic. Why something fixed is considered a good design ? — MTK, Jun 11 '17 at 13:43
The design is still in the project state ... for that I try various scenarios — MTK, Jun 11 '17 at 13:59

Keith John Hutchison · Accepted Answer · 2017-06-13T04:17:20.133

You can create very dynamic queries in mysql.

An example for your question -- now with added SQL injection protection!

DELIMITER ;;

DROP PROCEDURE IF EXISTS insert_into_table_columns_values ;;

CREATE DEFINER=`root`@`localhost` PROCEDURE insert_into_table_columns_values(IN tableName_ text, IN columns_ text, IN values_ text )
BEGIN

    DECLARE tableText text ;
    DECLARE columnText text ;
    DECLARE valuesText text ;

    set @tableText = tableName_ ;
    set @columnsText = columns_ ;
    set @valuesText = values_ ;

    SELECT LOCATE('select', @tableText) into @tableTextSelect ;
    SELECT LOCATE('delete', @tableText) into @tableTextDelete ;
    SELECT LOCATE('truncate', @tableText) into @tableTextTruncate ;
    SELECT LOCATE('drop', @tableText) into @tableTextDrop ;

    SELECT LOCATE('select', @columnsText) into @columnsTextSelect ;
    SELECT LOCATE('delete', @columnsText) into @columnsTextDelete ;
    SELECT LOCATE('truncate', @columnsText) into @columnsTextTruncate ;
    SELECT LOCATE('drop', @columnsText) into @columnsTextTextDrop ;

    SELECT LOCATE('select', @valuesText) into @valuesTextSelect ;
    SELECT LOCATE('delete', @valuesText) into @valuesTextDelete ;
    SELECT LOCATE('truncate', @valuesText) into @valuesTextTruncate ;
    SELECT LOCATE('drop', @valuesText) into @valuesTextTextDrop ;   

    if  @tableTextSelect 
        + @tableTextDelete 
        + @tableTextTruncate 
        + @tableTextDrop 

        + @columnsTextSelect 
        + @columnsTextDelete 
        + @columnsTextTruncate 
        + @columnsTextTextDrop

        + @valuesTextSelect 
        + @valuesTextDelete 
        + @valuesTextTruncate 
        + @valuesTextTextDrop

        = 0 then

        set @insertSQL = concat('insert into ', @tableText, @columnsText , ' values ',  @valuesText  , ' ; ') ;

        PREPARE insertStatement FROM @insertSQL;
        EXECUTE insertStatement ;
        DEALLOCATE PREPARE insertStatement; 

    else

        select 'invalid parameter(s)' error
        , @tableText table_
        , @columnsText columns_
        , @valuesText values_ 

        , @tableTextSelect 
        , @tableTextDelete 
        , @tableTextTruncate 
        , @tableTextDrop 

        , @columnsTextSelect 
        , @columnsTextDelete 
        , @columnsTextTruncate 
        , @columnsTextTextDrop

        , @valuesTextSelect 
        , @valuesTextDelete 
        , @valuesTextTruncate 
        , @valuesTextTextDrop ;

    end if ;



END;;
DELIMITER ;

-- example
call insert_into_table_columns_values('truncate employees', '(hire_date)', "('2017-06-12')" ) ;

-- results as json
{
    "data":
    [
        {
            "error": "invalid parameter(s)",
            "table_": "truncate employees",
            "columns_": "(hire_date)",
            "values_": "('2017-06-12')",
            "@tableTextSelect": 0,
            "@tableTextDelete": 0,
            "@tableTextTruncate": 1,
            "@tableTextDrop": 0,
            "@columnsTextSelect": 0,
            "@columnsTextDelete": 0,
            "@columnsTextTruncate": 0,
            "@columnsTextTextDrop": 0,
            "@valuesTextSelect": 0,
            "@valuesTextDelete": 0,
            "@valuesTextTruncate": 0,
            "@valuesTextTextDrop": 0
        }
    ]
}

Tested on MySQL.

Thank you for answer but that have injection risk. imagine if one of variables = test’,(select password from mysql.user where user=’root’ limit 0,1),’test2’)-- - — MTK, Jun 11 '17 at 13:24
Have you tried that ? It would appear to me the SQL statement would fail. — Keith John Hutchison, Jun 11 '17 at 22:09
Not this in particular, but this has happened to me with other test attempts of injection. For example the classic "'OR' 1 '=' 1 '; -" if i execute it from PHP PDO, injection it does not occur but if I have tried addslashes ("' OR '1' = '1'"); also with PDO bind parameters .. then the injection occurred.For that I have added the second comment : **or other like this ... until one works** — MTK, Jun 11 '17 at 22:57
see also one old mine question that I have here: https://stackoverflow.com/questions/43118164/mysql-pdo-and-stored-procedure-dynamic-sql-injection — MTK, Jun 11 '17 at 23:03

mysql (very) dynamic prepared statement

1 Answers1