MySql prepare statement - is it possible to parametrize column name or function name?











up vote
0
down vote

favorite












Lets say that I want to write a procedure allowing me to call certain function on certain column, for example:



call foo('min','age') -> SELECT min(age) FROM table;


I want my procedure to be safe from sql injection, therefore, I'm willing to use prepared statements and parametrize the input



SET @var = "SELECT ?(?) FROM table;"
PREPARE x FROM @var;
EXECUTE x USING a, b;


Where a and b are input parameters, function and column, respectively.



However, it doesnt seem to be possible - InnoDB keeps throwing an error whenever I want to execute this statement.



Is it possible to solve this this way, or I need to resort to whitelisting?



EDIT:
Full code:



create procedure test(in func varchar(20), in col varchar(20))
begin
set @f = func;
set @c = col;
set @sql = "select ?(?) from table;";
prepare x from @sql;
execute x using @f, @c;
end;


calling:



call test('min','age');


Full error:




[42000][1064] You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near '(?) from table' at line 1











share|improve this question
























  • Please add more context to your problem. It is currently unclear.
    – Madhur Bhaiya
    Nov 10 at 9:44










  • I am sorry for asking something that proved to be confusing, I have edited my question.
    – LaTeXEnthusiast
    Nov 10 at 20:53










  • What is the error message thrown by Innodb ?? Share the complete code your are using to create the stored procedure; and also how you are calling it.
    – Madhur Bhaiya
    Nov 10 at 21:31










  • Thank you for your feedback, I have added more detail.
    – LaTeXEnthusiast
    Nov 10 at 22:42















up vote
0
down vote

favorite












Lets say that I want to write a procedure allowing me to call certain function on certain column, for example:



call foo('min','age') -> SELECT min(age) FROM table;


I want my procedure to be safe from sql injection, therefore, I'm willing to use prepared statements and parametrize the input



SET @var = "SELECT ?(?) FROM table;"
PREPARE x FROM @var;
EXECUTE x USING a, b;


Where a and b are input parameters, function and column, respectively.



However, it doesnt seem to be possible - InnoDB keeps throwing an error whenever I want to execute this statement.



Is it possible to solve this this way, or I need to resort to whitelisting?



EDIT:
Full code:



create procedure test(in func varchar(20), in col varchar(20))
begin
set @f = func;
set @c = col;
set @sql = "select ?(?) from table;";
prepare x from @sql;
execute x using @f, @c;
end;


calling:



call test('min','age');


Full error:




[42000][1064] You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near '(?) from table' at line 1











share|improve this question
























  • Please add more context to your problem. It is currently unclear.
    – Madhur Bhaiya
    Nov 10 at 9:44










  • I am sorry for asking something that proved to be confusing, I have edited my question.
    – LaTeXEnthusiast
    Nov 10 at 20:53










  • What is the error message thrown by Innodb ?? Share the complete code your are using to create the stored procedure; and also how you are calling it.
    – Madhur Bhaiya
    Nov 10 at 21:31










  • Thank you for your feedback, I have added more detail.
    – LaTeXEnthusiast
    Nov 10 at 22:42













up vote
0
down vote

favorite









up vote
0
down vote

favorite











Lets say that I want to write a procedure allowing me to call certain function on certain column, for example:



call foo('min','age') -> SELECT min(age) FROM table;


I want my procedure to be safe from sql injection, therefore, I'm willing to use prepared statements and parametrize the input



SET @var = "SELECT ?(?) FROM table;"
PREPARE x FROM @var;
EXECUTE x USING a, b;


Where a and b are input parameters, function and column, respectively.



However, it doesnt seem to be possible - InnoDB keeps throwing an error whenever I want to execute this statement.



Is it possible to solve this this way, or I need to resort to whitelisting?



EDIT:
Full code:



create procedure test(in func varchar(20), in col varchar(20))
begin
set @f = func;
set @c = col;
set @sql = "select ?(?) from table;";
prepare x from @sql;
execute x using @f, @c;
end;


calling:



call test('min','age');


Full error:




[42000][1064] You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near '(?) from table' at line 1











share|improve this question















Lets say that I want to write a procedure allowing me to call certain function on certain column, for example:



call foo('min','age') -> SELECT min(age) FROM table;


I want my procedure to be safe from sql injection, therefore, I'm willing to use prepared statements and parametrize the input



SET @var = "SELECT ?(?) FROM table;"
PREPARE x FROM @var;
EXECUTE x USING a, b;


Where a and b are input parameters, function and column, respectively.



However, it doesnt seem to be possible - InnoDB keeps throwing an error whenever I want to execute this statement.



Is it possible to solve this this way, or I need to resort to whitelisting?



EDIT:
Full code:



create procedure test(in func varchar(20), in col varchar(20))
begin
set @f = func;
set @c = col;
set @sql = "select ?(?) from table;";
prepare x from @sql;
execute x using @f, @c;
end;


calling:



call test('min','age');


Full error:




[42000][1064] You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near '(?) from table' at line 1








mysql sql






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 10 at 22:42

























asked Nov 9 at 21:45









LaTeXEnthusiast

205




205












  • Please add more context to your problem. It is currently unclear.
    – Madhur Bhaiya
    Nov 10 at 9:44










  • I am sorry for asking something that proved to be confusing, I have edited my question.
    – LaTeXEnthusiast
    Nov 10 at 20:53










  • What is the error message thrown by Innodb ?? Share the complete code your are using to create the stored procedure; and also how you are calling it.
    – Madhur Bhaiya
    Nov 10 at 21:31










  • Thank you for your feedback, I have added more detail.
    – LaTeXEnthusiast
    Nov 10 at 22:42


















  • Please add more context to your problem. It is currently unclear.
    – Madhur Bhaiya
    Nov 10 at 9:44










  • I am sorry for asking something that proved to be confusing, I have edited my question.
    – LaTeXEnthusiast
    Nov 10 at 20:53










  • What is the error message thrown by Innodb ?? Share the complete code your are using to create the stored procedure; and also how you are calling it.
    – Madhur Bhaiya
    Nov 10 at 21:31










  • Thank you for your feedback, I have added more detail.
    – LaTeXEnthusiast
    Nov 10 at 22:42
















Please add more context to your problem. It is currently unclear.
– Madhur Bhaiya
Nov 10 at 9:44




Please add more context to your problem. It is currently unclear.
– Madhur Bhaiya
Nov 10 at 9:44












I am sorry for asking something that proved to be confusing, I have edited my question.
– LaTeXEnthusiast
Nov 10 at 20:53




I am sorry for asking something that proved to be confusing, I have edited my question.
– LaTeXEnthusiast
Nov 10 at 20:53












What is the error message thrown by Innodb ?? Share the complete code your are using to create the stored procedure; and also how you are calling it.
– Madhur Bhaiya
Nov 10 at 21:31




What is the error message thrown by Innodb ?? Share the complete code your are using to create the stored procedure; and also how you are calling it.
– Madhur Bhaiya
Nov 10 at 21:31












Thank you for your feedback, I have added more detail.
– LaTeXEnthusiast
Nov 10 at 22:42




Thank you for your feedback, I have added more detail.
– LaTeXEnthusiast
Nov 10 at 22:42












2 Answers
2






active

oldest

votes

















up vote
1
down vote



accepted










You cannot parametrize column/table/function name/alias. As, PREPARE statement only allow "values" part of the SQL query to be used as parameters. Function/Table/Column name/alias are used to determine the validity of the SQL statement; and thus cannot be changed during run-time execution. Changing it at execution time would potentially alter whether the SQL statement was valid.



You can think of it as compiling a code; hence the compiler must know all the function/class name(s) etc for creating a valid executable (yes, we can do dynamic classes, but that is rare). On the other hand, we can change input "values" to the program, but generally cannot change the operations to be done on the input data.



Also, MySQL server would consider the parameters as literals, and apply quotes around them, before using them in query execution.



Now, in your case, you can still use the function name as parameter for Stored procedure, and generate the query string using that. But you cannot use it as a parameter for the query itself.



delimiter $$
create procedure test(in func varchar(20), in col varchar(20))
begin

set @c = col;

-- use concat function to generate the query string using func parameter
set @sql = concat('select ', func, '(?) from table');

-- prepare the statement
prepare stmt from @sql;

-- execute
execute x using @c;

-- don't forget to deallocate the prepared statement
deallocate prepare stmt;
end$$
delimiter ;





share|improve this answer





















  • Thank you for your answer. I think that now it is better to use typical whitelisting, as the approach shown by you is sql-injection-prone.
    – LaTeXEnthusiast
    Nov 12 at 17:22










  • @LaTeXEnthusiast why do you think it is injection prone ? If you are using a function; MySQL parser would throw error if something other than a valid function is used. And regarding "values", those are anyways parametrized. Can you give an example of SQL injection possibility ? Depending on that, maybe some other solution can be figured out.
    – Madhur Bhaiya
    Nov 12 at 17:24










  • What if we used this as the func parameter: " * from users where id=1; /* "
    – LaTeXEnthusiast
    Nov 12 at 18:45










  • @LaTeXEnthusiast it will read as: 'select * from users where id=1; /*(?) from table Now MySQL will try to insert parametric value in it, and will not be able to do so and throw error. You can give it a try.
    – Madhur Bhaiya
    Nov 12 at 18:47


















up vote
0
down vote













The only way you can parameterize SQL keywords in a keyword is to use a dynamic query. The caveat is that dynamic queries tend to be slower than static queries, primarily because they can't easily be cached.



With the warning out of the way, something like this should work:



SET @query = "SELECT ?('?') FROM table";  
EXECUTE stmt USING 'count', 'id';





share|improve this answer





















    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














     

    draft saved


    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53233631%2fmysql-prepare-statement-is-it-possible-to-parametrize-column-name-or-function%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    1
    down vote



    accepted










    You cannot parametrize column/table/function name/alias. As, PREPARE statement only allow "values" part of the SQL query to be used as parameters. Function/Table/Column name/alias are used to determine the validity of the SQL statement; and thus cannot be changed during run-time execution. Changing it at execution time would potentially alter whether the SQL statement was valid.



    You can think of it as compiling a code; hence the compiler must know all the function/class name(s) etc for creating a valid executable (yes, we can do dynamic classes, but that is rare). On the other hand, we can change input "values" to the program, but generally cannot change the operations to be done on the input data.



    Also, MySQL server would consider the parameters as literals, and apply quotes around them, before using them in query execution.



    Now, in your case, you can still use the function name as parameter for Stored procedure, and generate the query string using that. But you cannot use it as a parameter for the query itself.



    delimiter $$
    create procedure test(in func varchar(20), in col varchar(20))
    begin

    set @c = col;

    -- use concat function to generate the query string using func parameter
    set @sql = concat('select ', func, '(?) from table');

    -- prepare the statement
    prepare stmt from @sql;

    -- execute
    execute x using @c;

    -- don't forget to deallocate the prepared statement
    deallocate prepare stmt;
    end$$
    delimiter ;





    share|improve this answer





















    • Thank you for your answer. I think that now it is better to use typical whitelisting, as the approach shown by you is sql-injection-prone.
      – LaTeXEnthusiast
      Nov 12 at 17:22










    • @LaTeXEnthusiast why do you think it is injection prone ? If you are using a function; MySQL parser would throw error if something other than a valid function is used. And regarding "values", those are anyways parametrized. Can you give an example of SQL injection possibility ? Depending on that, maybe some other solution can be figured out.
      – Madhur Bhaiya
      Nov 12 at 17:24










    • What if we used this as the func parameter: " * from users where id=1; /* "
      – LaTeXEnthusiast
      Nov 12 at 18:45










    • @LaTeXEnthusiast it will read as: 'select * from users where id=1; /*(?) from table Now MySQL will try to insert parametric value in it, and will not be able to do so and throw error. You can give it a try.
      – Madhur Bhaiya
      Nov 12 at 18:47















    up vote
    1
    down vote



    accepted










    You cannot parametrize column/table/function name/alias. As, PREPARE statement only allow "values" part of the SQL query to be used as parameters. Function/Table/Column name/alias are used to determine the validity of the SQL statement; and thus cannot be changed during run-time execution. Changing it at execution time would potentially alter whether the SQL statement was valid.



    You can think of it as compiling a code; hence the compiler must know all the function/class name(s) etc for creating a valid executable (yes, we can do dynamic classes, but that is rare). On the other hand, we can change input "values" to the program, but generally cannot change the operations to be done on the input data.



    Also, MySQL server would consider the parameters as literals, and apply quotes around them, before using them in query execution.



    Now, in your case, you can still use the function name as parameter for Stored procedure, and generate the query string using that. But you cannot use it as a parameter for the query itself.



    delimiter $$
    create procedure test(in func varchar(20), in col varchar(20))
    begin

    set @c = col;

    -- use concat function to generate the query string using func parameter
    set @sql = concat('select ', func, '(?) from table');

    -- prepare the statement
    prepare stmt from @sql;

    -- execute
    execute x using @c;

    -- don't forget to deallocate the prepared statement
    deallocate prepare stmt;
    end$$
    delimiter ;





    share|improve this answer





















    • Thank you for your answer. I think that now it is better to use typical whitelisting, as the approach shown by you is sql-injection-prone.
      – LaTeXEnthusiast
      Nov 12 at 17:22










    • @LaTeXEnthusiast why do you think it is injection prone ? If you are using a function; MySQL parser would throw error if something other than a valid function is used. And regarding "values", those are anyways parametrized. Can you give an example of SQL injection possibility ? Depending on that, maybe some other solution can be figured out.
      – Madhur Bhaiya
      Nov 12 at 17:24










    • What if we used this as the func parameter: " * from users where id=1; /* "
      – LaTeXEnthusiast
      Nov 12 at 18:45










    • @LaTeXEnthusiast it will read as: 'select * from users where id=1; /*(?) from table Now MySQL will try to insert parametric value in it, and will not be able to do so and throw error. You can give it a try.
      – Madhur Bhaiya
      Nov 12 at 18:47













    up vote
    1
    down vote



    accepted







    up vote
    1
    down vote



    accepted






    You cannot parametrize column/table/function name/alias. As, PREPARE statement only allow "values" part of the SQL query to be used as parameters. Function/Table/Column name/alias are used to determine the validity of the SQL statement; and thus cannot be changed during run-time execution. Changing it at execution time would potentially alter whether the SQL statement was valid.



    You can think of it as compiling a code; hence the compiler must know all the function/class name(s) etc for creating a valid executable (yes, we can do dynamic classes, but that is rare). On the other hand, we can change input "values" to the program, but generally cannot change the operations to be done on the input data.



    Also, MySQL server would consider the parameters as literals, and apply quotes around them, before using them in query execution.



    Now, in your case, you can still use the function name as parameter for Stored procedure, and generate the query string using that. But you cannot use it as a parameter for the query itself.



    delimiter $$
    create procedure test(in func varchar(20), in col varchar(20))
    begin

    set @c = col;

    -- use concat function to generate the query string using func parameter
    set @sql = concat('select ', func, '(?) from table');

    -- prepare the statement
    prepare stmt from @sql;

    -- execute
    execute x using @c;

    -- don't forget to deallocate the prepared statement
    deallocate prepare stmt;
    end$$
    delimiter ;





    share|improve this answer












    You cannot parametrize column/table/function name/alias. As, PREPARE statement only allow "values" part of the SQL query to be used as parameters. Function/Table/Column name/alias are used to determine the validity of the SQL statement; and thus cannot be changed during run-time execution. Changing it at execution time would potentially alter whether the SQL statement was valid.



    You can think of it as compiling a code; hence the compiler must know all the function/class name(s) etc for creating a valid executable (yes, we can do dynamic classes, but that is rare). On the other hand, we can change input "values" to the program, but generally cannot change the operations to be done on the input data.



    Also, MySQL server would consider the parameters as literals, and apply quotes around them, before using them in query execution.



    Now, in your case, you can still use the function name as parameter for Stored procedure, and generate the query string using that. But you cannot use it as a parameter for the query itself.



    delimiter $$
    create procedure test(in func varchar(20), in col varchar(20))
    begin

    set @c = col;

    -- use concat function to generate the query string using func parameter
    set @sql = concat('select ', func, '(?) from table');

    -- prepare the statement
    prepare stmt from @sql;

    -- execute
    execute x using @c;

    -- don't forget to deallocate the prepared statement
    deallocate prepare stmt;
    end$$
    delimiter ;






    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered Nov 12 at 5:00









    Madhur Bhaiya

    16.4k52136




    16.4k52136












    • Thank you for your answer. I think that now it is better to use typical whitelisting, as the approach shown by you is sql-injection-prone.
      – LaTeXEnthusiast
      Nov 12 at 17:22










    • @LaTeXEnthusiast why do you think it is injection prone ? If you are using a function; MySQL parser would throw error if something other than a valid function is used. And regarding "values", those are anyways parametrized. Can you give an example of SQL injection possibility ? Depending on that, maybe some other solution can be figured out.
      – Madhur Bhaiya
      Nov 12 at 17:24










    • What if we used this as the func parameter: " * from users where id=1; /* "
      – LaTeXEnthusiast
      Nov 12 at 18:45










    • @LaTeXEnthusiast it will read as: 'select * from users where id=1; /*(?) from table Now MySQL will try to insert parametric value in it, and will not be able to do so and throw error. You can give it a try.
      – Madhur Bhaiya
      Nov 12 at 18:47


















    • Thank you for your answer. I think that now it is better to use typical whitelisting, as the approach shown by you is sql-injection-prone.
      – LaTeXEnthusiast
      Nov 12 at 17:22










    • @LaTeXEnthusiast why do you think it is injection prone ? If you are using a function; MySQL parser would throw error if something other than a valid function is used. And regarding "values", those are anyways parametrized. Can you give an example of SQL injection possibility ? Depending on that, maybe some other solution can be figured out.
      – Madhur Bhaiya
      Nov 12 at 17:24










    • What if we used this as the func parameter: " * from users where id=1; /* "
      – LaTeXEnthusiast
      Nov 12 at 18:45










    • @LaTeXEnthusiast it will read as: 'select * from users where id=1; /*(?) from table Now MySQL will try to insert parametric value in it, and will not be able to do so and throw error. You can give it a try.
      – Madhur Bhaiya
      Nov 12 at 18:47
















    Thank you for your answer. I think that now it is better to use typical whitelisting, as the approach shown by you is sql-injection-prone.
    – LaTeXEnthusiast
    Nov 12 at 17:22




    Thank you for your answer. I think that now it is better to use typical whitelisting, as the approach shown by you is sql-injection-prone.
    – LaTeXEnthusiast
    Nov 12 at 17:22












    @LaTeXEnthusiast why do you think it is injection prone ? If you are using a function; MySQL parser would throw error if something other than a valid function is used. And regarding "values", those are anyways parametrized. Can you give an example of SQL injection possibility ? Depending on that, maybe some other solution can be figured out.
    – Madhur Bhaiya
    Nov 12 at 17:24




    @LaTeXEnthusiast why do you think it is injection prone ? If you are using a function; MySQL parser would throw error if something other than a valid function is used. And regarding "values", those are anyways parametrized. Can you give an example of SQL injection possibility ? Depending on that, maybe some other solution can be figured out.
    – Madhur Bhaiya
    Nov 12 at 17:24












    What if we used this as the func parameter: " * from users where id=1; /* "
    – LaTeXEnthusiast
    Nov 12 at 18:45




    What if we used this as the func parameter: " * from users where id=1; /* "
    – LaTeXEnthusiast
    Nov 12 at 18:45












    @LaTeXEnthusiast it will read as: 'select * from users where id=1; /*(?) from table Now MySQL will try to insert parametric value in it, and will not be able to do so and throw error. You can give it a try.
    – Madhur Bhaiya
    Nov 12 at 18:47




    @LaTeXEnthusiast it will read as: 'select * from users where id=1; /*(?) from table Now MySQL will try to insert parametric value in it, and will not be able to do so and throw error. You can give it a try.
    – Madhur Bhaiya
    Nov 12 at 18:47












    up vote
    0
    down vote













    The only way you can parameterize SQL keywords in a keyword is to use a dynamic query. The caveat is that dynamic queries tend to be slower than static queries, primarily because they can't easily be cached.



    With the warning out of the way, something like this should work:



    SET @query = "SELECT ?('?') FROM table";  
    EXECUTE stmt USING 'count', 'id';





    share|improve this answer

























      up vote
      0
      down vote













      The only way you can parameterize SQL keywords in a keyword is to use a dynamic query. The caveat is that dynamic queries tend to be slower than static queries, primarily because they can't easily be cached.



      With the warning out of the way, something like this should work:



      SET @query = "SELECT ?('?') FROM table";  
      EXECUTE stmt USING 'count', 'id';





      share|improve this answer























        up vote
        0
        down vote










        up vote
        0
        down vote









        The only way you can parameterize SQL keywords in a keyword is to use a dynamic query. The caveat is that dynamic queries tend to be slower than static queries, primarily because they can't easily be cached.



        With the warning out of the way, something like this should work:



        SET @query = "SELECT ?('?') FROM table";  
        EXECUTE stmt USING 'count', 'id';





        share|improve this answer












        The only way you can parameterize SQL keywords in a keyword is to use a dynamic query. The caveat is that dynamic queries tend to be slower than static queries, primarily because they can't easily be cached.



        With the warning out of the way, something like this should work:



        SET @query = "SELECT ?('?') FROM table";  
        EXECUTE stmt USING 'count', 'id';






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 9 at 22:06









        Mike Dinescu

        38k879118




        38k879118






























             

            draft saved


            draft discarded



















































             


            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53233631%2fmysql-prepare-statement-is-it-possible-to-parametrize-column-name-or-function%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Bressuire

            Vorschmack

            Quarantine