Member Avatar for 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

Edited by riahc3
  • 3 Contributors
  • 5 Replies
  • 656 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by riahc3

Dani AI

Generated

Short version: stop concatenating user input into SQL, validate strictly on the server, and use parameterized queries (PDO or MySQLi). As said, use bound parameters; as pointed out, stored procedures do not automatically make a query safe — they only help if you still pass values as parameters. Because your table is static (), hard-code it in the SQL (or use a strict allowlist) — identifiers cannot be safely bound like values.

Example server-side pattern (PDO + strict server-side validation). This returns the same JSON array shape your client expects (rows as numeric arrays):

<?php
// mysqlcall.php
$id = filter_input(INPUT_POST, 'someid', FILTER_VALIDATE_INT);
if ($id === false || $id === null) {
    header('Content-Type: application/json');
    echo json_encode([]); // client handles empty result
    exit;
}

$pdo = new PDO('mysql:host=localhost;dbname=yourdb;charset=utf8mb4','dbuser','dbpass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$stmt = $pdo->prepare('SELECT name, age FROM `thenameofthetable` WHERE idp = :id');
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_NUM);

header('Content-Type: application/json');
echo json_encode($rows);

If you must use a stored procedure, call it with a bound parameter the same way (prepared CALL). Example:

$stmt = $pdo->prepare('CALL get_person_by_id(:id)');
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_NUM);

Practical tips: never trust client-side validation, never expose raw DB errors to the browser (log them instead), avoid the old mysql_* extension, and do not try to bind table/column names — use a whitelist if they must be dynamic. On the client side use POST (send an object or JSON) and keep async true; expect and parse JSON (dataType: 'json' or fetch + response.json()).

Recommended Answers

All 5 Replies

Member Avatar for 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.

Member Avatar for 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.

Member Avatar for 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

Member Avatar for 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 and gets the age of Alice then would make more sense to have

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?

Member Avatar for 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....

Edited by riahc3
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.