Professional Web Applications Themes

stored procedure help - MySQL

This is driving me crazy. I'm new to stored procedures and I can't find any help for this one anywhere. I'm attempting to create a procedure that will accept as parameters both a column name and a value and then update a table so that the passed value will be placed into the passed column name. Is it not possible to pass a column name? I can't find an example anywhere. Here is a start, but it just returns errors. Can someone tell me what is wrong? Thanks Jeff DELIMITER $$ DROP PROCEDURE IF EXISTS `x`.`test`$$ CREATE PROCEDURE `x`.`test`(PColumn varchar(50),PValue ...

  1. #1

    Default stored procedure help



    This is driving me crazy. I'm new to stored procedures and I can't find any
    help for this one anywhere.

    I'm attempting to create a procedure that will accept as parameters both a
    column name and a value
    and then update a table so that the passed value will be placed into the
    passed column name.

    Is it not possible to pass a column name? I can't find an example anywhere.

    Here is a start, but it just returns errors. Can someone tell me what is
    wrong?

    Thanks

    Jeff


    DELIMITER $$

    DROP PROCEDURE IF EXISTS `x`.`test`$$

    CREATE PROCEDURE `x`.`test`(PColumn varchar(50),PValue int)
    BEGIN
    Declare y varchar(50);
    set y = PColumn;
    update table set y = PValue where ID = 47;
    END$$
    DELIMITER ;



    --
    Posted via a free Usenet account from http://www.teranews.com

    Jeff Guest

  2. #2

    Default Re: stored procedure help

    Jeff wrote: 

    Sorry, you can't use a variable for a column name.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: stored procedure help

    You could do this with a prepared statement.

    R
    "Jerry Stuckle" <net> wrote in message
    news:com... 
    >
    > Sorry, you can't use a variable for a column name.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]


    LittleRob Guest

  4. #4

    Default Re: stored procedure help

    I disagree, Jerry.

    Jeff, you can do it using prepared statements inside of the procedure.

    Here an example :

    CREATE PROCEDURE set_value_in_column(
    IN _value VARCHAR(100),
    IN _column VARCHAR(128))

    BEGIN
    DECLARE prepared VARCHAR(300);
    SET prepared=CONCAT(
    'UPDATE table SET ',_column,'=',_value,'WHERE condition;');
    SET prep=prepared;
    PREPARE _update FROM prep;
    EXECUTE _update;
    DEALLOCATE PREPARE _update;
    END;

    Be very careful though with the data you give as input, you might have to
    check for injections more carefully, because you are creating SQL code
    dynamically within the procedure, and thus your are not protected from
    injections anymore.

    ---
    You got to realize you can kill somebody with kindness too.
    - Bob Dylan

    subtenante Guest

  5. #5

    Default Re: stored procedure help


    "subtenante" <com> wrote in message
    news:461b3346$0$2444$free.fr... 
     


    Thanks much.

    ....here's a follow-up:

    if my CODE were responsible for defining/passing the column parameter
    without the ability of the user to modify that code, while the user could
    modify only the value that subsequently would be passed, would I be correct
    in the assumption that the sql injection would then be prevented?

    The basic situation (without boring you with minor details - and this is not
    a typical application) is I could do the same as above with a large number
    of stored procedures - one for every text-box entry in the application, and
    those stored procedures would have to be created dynamically by the code, or
    I could do as in your example where the code could simply pass the column
    name to the procedure as one parameter while the user would be entering the
    value to store and therefore I would need only a single stored procedure for
    storing and a single stored procedure for retrieving all of the different
    columns.

    Jeff



    --
    Posted via a free Usenet account from http://www.teranews.com

    Jeff Guest

  6. #6

    Default Re: stored procedure help

    ===
    "Jeff" <com> wrote:
    The basic situation (without boring you with minor details - and this is not
    a typical application) is I could do the same as above with a large number
    of stored procedures - one for every text-box entry in the application, and
    those stored procedures would have to be created dynamically by the code, or
    I could do as in your example where the code could simply pass the column
    name to the procedure as one parameter while the user would be entering the
    value to store and therefore I would need only a single stored procedure for
    storing and a single stored procedure for retrieving all of the different
    columns.
    ===

    Code generating code generating code, huh ? Smart.
    Anyways, whenever you feed any procedure with user input when the code is
    self-generated, you have to check for it carefully, no matter the level of
    generation. As far as i understand your trick, the user input will still have
    to appear in a CONCAT somewhere, and that is dangerous. If, by a mean i don't
    get, you avoid this CONCAT of automatic code mixed with user input, it may be
    secure. But i'm not sure i see how you could do this.

    Depending on the rights you can give to the account executing that procedure,
    and if you use very strict naming conventions for your rows (always adding a
    unique prefix for each row of the same table), you might also be able to use
    the information_schema database to retrieve the table name for that specific
    column you want to edit, thus not needing to have the second procedure (a
    right SELECT in the information_schema COLUMNS table would do it :

    SELECT `information_schema`.`COLUMNS`.`TABLE_NAME`
    FROM `information_schema`.`COLUMNS`
    WHERE `information_schema`.`COLUMNS`.`COLUMN_NAME`='...' ;

    ). This way you can change the shape of your database without needing to
    update your second procedure every time. At least it may be a save of time
    while you are still in production.

    ---
    You got to realize you can kill somebody with kindness too.
    - Bob Dylan
    (There is no z in my email address)

    subtenante Guest

  7. #7

    Default Re: stored procedure help


    "subtenante" <com> wrote in message
    news:461c5171$0$15530$free.fr... 

    I think that you somewhat understood what I was getting at, but I guess that
    I didn't fully explain.

    What I was attempting to explain was that this unusual application would
    have required me to constantly create, delete and modify entire databases
    and accompanying procedures and this would be time consuming. So I made two
    different applications - one web based via asp.net and another a stand-alone
    vb executable that only I will have access to. The stand-alone .exe will
    automatically create the database and most of the procedures using an
    elevated level of access. The web app would also have two parts, one
    accessible only to those working with me - that aspect would generate
    procedures to update and select columns based upon other entires that the
    helpers (not all that computer literate) would generate. The assistants are
    trustworty and I don't have to worry about sql injection from them (unless
    someone hacked into their machines). ...but the other side of the app is
    essentially open to the pubic and there are text box entires there. I won't
    know what columns will eventually end up in a few tables since they will be
    generated by the assistants running my code. I also have created code that
    will run when the assistants hit an icon on the secure part of the web page
    that will generate a single update procedure for each column they create. So
    for the ultimate end user, there won't be any code that contains a
    concatenation if I use the method that I was previously describing. ...but
    this really isn't very neat, because it generates a large number of
    procedures that all duplicate one another with only the column specification
    differing. E.g., proc1 update table set col1 = parm1 -- proc2 update table
    set col2 = parm1 -- proc3 update table set col3 = parm1 etc. ...but the
    entire purpose of doing this was to avoid the injection possibility. ...but
    your solution sounds like it is also open to injection without checking the
    user's entries.

    As I understand visual studio 2005, which is what I'm using, it by default
    has client side validation that checks for improper characters. My code then
    validates the text entry and announces an error if there are characters that
    I have not permitted. The server then checks again in the event that someone
    bypasses the actual webpage and sends something directly to the server. I
    was advised by someone much more knowledgable than I to also use stored
    procedures, presumably to prevent someone from hacking into the DB directly
    through ODBC without going through IIS - but I really don't understand how
    someone might accomplish this.

    ....anyway, your example was a good alternative, but I've just spent a fair
    amount of time unsuccessfully attempting to get it to work. I'm using mysql
    version 5. Everything works other than the parameter for the value. It seems
    that regardless how I modify the code I get the same error telling me that
    the proc is considering the value to be a column. Could something have
    changed in the new mysql version to prevent this type of concatenation?

    ....perhap you will see something wrong with my code (I'm still new to this -
    used almost your example also, and that returns the same error)

    Error Code : 1054
    Unknown column 'testingsss' in 'field list'
    (0 ms taken)

    in response to something like this:

    call testing('Q3', 'testingsss');


    DELIMITER $$
    CREATE PROCEDURE testing(_columnX VARCHAR(128), _valueX VARCHAR(50))

    BEGIN
    DECLARE statement VARCHAR(300);
    set statement = Concat('Update Table set ',_columnX,' = ',_valueX,' where
    ID = 105;');
    PREPARE stmt FROM statement;
    Execute stmt;
    end$$

    DELIMITER ;



    --
    Posted via a free Usenet account from http://www.teranews.com

    Jeff Guest

  8. #8

    Default Re: stored procedure help

    > The assistants are 
     
    That's just the cheaters who are not, and you never know where they
    are.
     

    Ok, if you create one procedure per column then you don't need to
    check for injections (unless you want to know who is trying to do
    these injections...).
     

    Ok.

    CREATE PROCEDURE `testing`(_columnX VARCHAR(128), _valueX VARCHAR(50))
    BEGIN
    set statement = Concat("Update Tablep set ",_columnX," =
    '",_valueX,"' where ID = '105';");
    PREPARE stmt FROM statement;
    Execute stmt;
    end

    First the textual value might be between quotes, then do not name a
    table "Table", it is a reserved word for MySQL and puts a mess in your
    query while parsing it.

    What i pasted there worked fine for me.


    subtenante Guest

  9. #9

    Default Re: stored procedure help

    Jeff wrote: 
    >
    > I think that you somewhat understood what I was getting at, but I guess
    > that I didn't fully explain.
    >
    > What I was attempting to explain was that this unusual application would
    > have required me to constantly create, delete and modify entire
    > databases and accompanying procedures and this would be time consuming.[/ref]

    Why are you constantly creating, modifying and deleting databases?
    Unless this is a DB admin tool such as PhPMyAdmin, MySQL Administrator,
    etc., that's completely unnecessary. Are you sure you have a good db
    design?

    In over 20 years of SQL programming I've never had to do this.


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  10. #10

    Default Re: stored procedure help


    "subtenante" <com> wrote in message
    news:googlegroups.com...
     

    Okay, I got it. As is typical, I missed something stupid, a missing quote. I
    now have it working.
    ....but should have explained that I wasn't actually using "table" ...just
    took out the real value for the post to shorten things.
    ....didn't realize that it was reserved.
    Jeff



    --
    Posted via a free Usenet account from http://www.teranews.com

    Jeff Guest

  11. #11

    Default Re: stored procedure help


    "Jerry Stuckle" <net> wrote in message
    news:com... 
    > Unless this is a DB admin tool such as PhPMyAdmin, MySQL Administrator,
    > etc., that's completely unnecessary. Are you sure you have a good db
    > design?
    >
    > In over 20 years of SQL programming I've never had to do this.[/ref]

    As I mentioned, it isn't a typical application. It was intended as a small
    scale tool to assist statisticians with data collection and the transfer of
    that data to statistical software packages. Because the ultimate purpose is
    to use each database for a short period of time to collect data and then
    rapidly have statistical software access and remove the data and then start
    another round with tables of a completely different structure, the app won't
    really act like the typical db application that you're used to. I'm mostly a
    statistician dabbling in programming who is largely self-taught. ...so I can
    pick this stuff up quickly, but I don't have much formal programming
    training and sometimes don't know where to start looking. After I get
    started in a new area, I can usually find the info I need on the web or
    elsewhere. I have some database and web people helping me and I keep having
    to also explain to them what I'm attempting to accomplish because most of my
    goals are a bit foreign to what they've seen before. After I explain at
    length, they eventually get it, and then just smile and say something like,
    "if it works, I guess it isn't stupid."

    Thanks for the help.




    --
    Posted via a free Usenet account from http://www.teranews.com

    Jeff Guest

  12. #12

    Default Re: stored procedure help

    Jeff wrote: 
    >> Unless this is a DB admin tool such as PhPMyAdmin, MySQL Administrator,
    >> etc., that's completely unnecessary. Are you sure you have a good db
    >> design?
    >>
    >> In over 20 years of SQL programming I've never had to do this.[/ref]
    >
    > As I mentioned, it isn't a typical application. It was intended as a small
    > scale tool to assist statisticians with data collection and the transfer of
    > that data to statistical software packages. Because the ultimate purpose is
    > to use each database for a short period of time to collect data and then
    > rapidly have statistical software access and remove the data and then start
    > another round with tables of a completely different structure, the app won't
    > really act like the typical db application that you're used to. I'm mostly a
    > statistician dabbling in programming who is largely self-taught. ...so I can
    > pick this stuff up quickly, but I don't have much formal programming
    > training and sometimes don't know where to start looking. After I get
    > started in a new area, I can usually find the info I need on the web or
    > elsewhere. I have some database and web people helping me and I keep having
    > to also explain to them what I'm attempting to accomplish because most of my
    > goals are a bit foreign to what they've seen before. After I explain at
    > length, they eventually get it, and then just smile and say something like,
    > "if it works, I guess it isn't stupid."
    >
    > Thanks for the help.
    >
    >
    >
    >[/ref]

    In that case you could use a course on database design. I've done
    statistical reports before (mainly interfacing with SAS) - and I still
    stand by my statement that you shouldn't have to keep changing the
    design of the database.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  13. #13

    Default Re: stored procedure help


    "Jerry Stuckle" <net> wrote in message
    news:com... 


    I could use a few courses. ...wouldn't argue with you at all. ...but time is
    currently at a premium.
    Keep in mind, as I mentioned before, I'm largely self-taught. So if someone
    tells me that I should use a stored procedure to perform a task I wish to
    do, I can generally read on my own how to code stored procedure. ...but
    without that initial hint, it would take me a large amount of time to get to
    where I need to go on my own.

    The people giving me a hand on occasion have taught me a good bit in a short
    period of time. ...and they are nice enough to only smile a bit when they
    see some of how I've created this thing.

    I'm sure that you are correct in that someone more experienced could do this
    much better.

    Thanks for the help.

    Jeff


    --
    Posted via a free Usenet account from http://www.teranews.com

    Jeff Guest

  14. #14

    Default Re: stored procedure help

    On Wed, 11 Apr 2007 23:40:21 -0500, Jeff wrote: 
    >
    >
    > I could use a few courses. ...wouldn't argue with you at all. ...but time is
    > currently at a premium.
    > Keep in mind, as I mentioned before, I'm largely self-taught. So if someone
    > tells me that I should use a stored procedure to perform a task I wish to
    > do, I can generally read on my own how to code stored procedure. ...but
    > without that initial hint, it would take me a large amount of time to get to
    > where I need to go on my own.[/ref]

    Then perhaps you shouldn't be telling us what tools and methods you are
    using and why they're fighting with you. Instead, try telling us the
    *task* and we can show you designs that don't fight back.

    --
    The plural of datum is not "facts".
    A collection of facts is not "knowledge".
    Peter Guest

Similar Threads

  1. stored procedure value
    By -D- in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 28th, 07:48 PM
  2. help with a stored procedure
    By Jason Tesser in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 3rd, 05:35 PM
  3. Stored procedure
    By Uri in forum Microsoft SQL / MS SQL Server
    Replies: 13
    Last Post: August 19th, 03:23 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139