Call a MySQL stored procedure using PHP?

[riahc3]

I currently have

$tableName="thenameofthetable";
$someid=$_GET["someidthruget"];
$result = mysql_query("SELECT name,age FROM ". $tableName. " WHERE idp=". $someid);   

Which looks really insecure.

How can I do it using a stored procedure which I think will make it a lot more secure?

Thanks

[pritaeas]

You can by creating the stored procedure with two params and use CALL yourprocedure(param1, param2).

Apart from that I advise to use MySQLi or PDO using bound parameters. If you insert table names like that be sure to do strict validations before using them. Using backticks around your table and columns names too.

[jkon]

Stored procedures rarely have to do with security and certainly not in your example. Of course you should use prepared statements along with PDO for example but you should validate data both client side and server side. Creating a stored procedure for a single statement doesn’t make any sense other than hiding it from you.

[riahc3]

You can by creating the stored procedure with two params and use CALL yourprocedure(param1, param2).

Apart from that I advise to use MySQLi or PDO using bound parameters. If you insert table names like that be sure to do strict validations before using them. Using backticks around your table and columns names too.

Stored procedures rarely have to do with security and certainly not in your example. Of course you should use prepared statements along with PDO for example but you should validate data both client side and server side. Creating a stored procedure for a single statement doesn’t make any sense other than hiding it from you.

Could you give me example (on that simple query) how to make it secure client/server side? Thank you

[jkon]

I will try to give an example but please first , take a moment to answer two questions that I have , and without that info an example could be completely something different from what you are doing.

First of all, what exactly are you doing? There could be many ideas depending on what you are doing. For example why you get the $someid via $_GET ? . Although _GET is as secure as _POST at the end of the day (not in how easy is to try infinite gets) it doesn’t make any sense to share with others an internal system variable like an ID, if you want for example to create a page where someone types http://www.example.com/Age/34 and gets the age of Alice then would make more sense to have http://www.example.com/Age/Alice

This is not only for security, not only for SEO (yes it is for both) but more it is for logic clearance.

Then you have the table name with a variable in the statement ($tableName) , why? What problem could drove you to such solution?

[riahc3]

Sure :) Ill try to answer all your questions.

Really, there isnt any reason between choosing GET and POST. My code is something like this:

$j.ajax({  // Start AJAX function                                     
            url: 'mysqlcall.php',                       
            async: false,
            data: "somethingipass="+somevariabledeclaredaboveigetfromapage,                        

            dataType: 'json',                    

                            success: function(data)          //on recieve of reply
                                { //start success function

                               var selecthtml='Im making a combo: <select name="combo" id="combo">'

                                    for (var i=0;i<data.length;i++)
                                        {    //start for  

                                            var row = data[i];          
                                            var name = row[0]; //first column result


                                            var age = row[1]; //second column result

                                            selecthtml=selecthtml+'<option value="'+age+'">'+name+'</option>';



                                        }  //end for
                                        selecthtml=selecthtml+'</select>';

                                        if (data.length!=0)
                                        {
                                            $j('#somediv').append(selecthtml);
                                            $j('#divforcombo').show();
                                        }
                                        else
                                        {
                                            $j('#divforcombo').hide();
                                        }






                                        } //end success



                    }); //end ajax function

So if I could do it via POST, great :) but no idea how to (or maybe I tried and it did not work)

Basically my code autoexplains but: Im making a combobox with text (name) and a value (age).

Also, the table is static. Thats why in the stored procedure I would have it static as well. I left if like this simply out of laziness as that variable could be static and thats it....