Tag: insert
Nabin Nepal (Starx) injection insert mysql pdo
June 17, 2013

Insert value to MySQL table safely using PDO

FriedBitz’s Question:

FriedBitzFriedBitz

Would this protect my INSERT from SQL Injections? and can I somehow shorten this code to make it look neater?

$db = new PDO('mysql:host=XXXXXXXXXX;dbname=XXXXXXXXX', 'XXXXXXXXX', 'XXXXXXXXXX');

// query MySQL to verify login
$query = $db->prepare("INSERT INTO login (username,password,name,email_add,age,country) VALUES (:username,:password,:name,:email_add,:age,:country)");
$query->execute(array(
':username' => $username, 
':password' => $password,
':name' => $name,
':email_add' => $email,
':age' => $age,
':country' => $country));
StarxStarx

YES, PDO Extension already removes all the injection vulnerabilities like that. Since you are binding params afters ward, you are doing it correctly too.

However, just to make the query at it safest state, specify the data type of the variable while binding them.

$query = $db->prepare('INSERT INTO login (username,password,name,email_add,age,country) VALUES (:username,:password,:name,:email_add,:age,:country)');

$query->bindParam(':username', $username, PDO::PARAM_STR, 20);
// and so on
$query -> execute();

More Details

Nabin Nepal (Starx) insert mysql php sql-insert sql-update
March 18, 2013

After MYSQL query return if the row was updated or inserted

Question by Joris Blanc

Joris BlancJoris Blanc

So I’m working on a part of my website where users can uprate and downrate content, this is stored one row per rating with a +1 for an upvote and a -1 for a downvote.

I also will be having a total of upvote - downvotes of the users profile page. The total of these votes will be stored in the mysql table of the users information.

My problem is that when someone votes something, lets say a downvote so I substract one to the total value of the votes on the users profile. But then if the user changes his mind and modifies the vote to an upvote well for the final value to be right I have to actually add 2 ( +1 to compensate the -1 and an other +1 for the upvote)

$sql = "    
    INSERT INTO `votes`
    (
        `added_by`, 
        `value`,
        `post_id`,
        `ip_created`
    )  VALUES (
        '".$logedInUser['User_ID']."',
        '".$value."',
        '".$this->post_id."',
        '".ip2()."'
    )
    ON DUPLICATE KEY UPDATE `value` = '".$value."'
";
$db->sql_query($sql);

I know that I can figure out a way to do it, but it will require a few other queries and I’m hopping there is a way to be able to return if it was inserted or updated. (I know that the query will only return TRUE or FALSE)

Answer by Starx

StarxStarx

Based on your current data structure, I think instead of the hack of adding 2 to balance the data when the user upvote and subtracting 2 when he downvotes again. Try this:

  1. You have to first remove the vote.
  2. Then cast another vote to make the change.
Nabin Nepal (Starx) arrays insert multidimensional mysql
April 16, 2012

Multidimensional array with many sub arrays and mysql – how to deal with it?

Question by Delicja

DelicjaDelicja

I have very long multidimensional array that have many sub arrays. I would like to insert some value from them into mysql. I would be gratefull for some tip how to deal with it. I would like to use a loop that put only some value to one or different table in database. How I can get value from for example [Things][Thing][k][value]? Thanks for any advice.

Array
(
[Data] => Array
    (
        [A] => Array
            (
                [B] => Array
                    (
                        [0] => Array
                            (
                                [C] => Array
                                    (
                                        [value] => some value1
                                    )
                                [D] => Array
                                    (
                                        [value] => some value2
                                    )
                                [E] => some value3
                            )
                        [1] => Array
                            (
                                [C] => Array
                                    (
                                        [value] => some value4
                                    )
                                [D] => Array
                                    (
                                        [value] => some value5
                                    )
                                [E] => 5
                            )
                    )
                [value] => 
            )
[Things] => Array
            (
                [Thing] => Array
                    (
                        [F] => Array
                            (
                                [value] => some value6
                            )
                        [G] => Array
                            (
                                [H] => Array
                                    (
                                        [0] => Array
                                            (
                                                [i] => Array
                                                    (
                                                        [value] => some value7
                                                    )
                                                [j] => Array
                                                    (
                                                        [value] => some value8
                                                        [value] => some value8
                                                    )
                                                [k] => Array
                                                    (
                                                        [value] => some value9
                                                    )
                                                [l] => Array
                                                    (
                                                        [value] => some value10
                                                    )
                                                [m] => some value11
                                                [n] => 
                                            )
                                        [1] => Array
                                            (
                                                [o] => Array
                                                    (
                                                        [value] => some value12
                                                    )
                                                [p] => Array
                                                    (
                                                        [value] => some value13
                                                    )
                                                [r] => Array
                                                    (
                                                        [value] => some value14
                                                    )
                                                [d] => Array
                                                    (
                                                        [value] => some value15
                                                    )
                                                [t] => some value16
                                                [u] => 
                                            )
                                    )
                                [value] => 
                            )
                            )
                            )           
                    [Thing2] => Array       
                    (

                            (...)

                            and so on...

In my database I have table for these things from array above. There is id in it of course and some other fields that are connected with other tables.
As you can see below I would like to get some values from array, insert it into Table Thing, and some of them put into
Table for field 4 and Table for field 5 and get id of them and put it into Table Thing. I’ll use stored procedure.
I would like to call it from php (I hope that this conception is good?). I have problem to get values that I want to get from this array so David Chan I’m looking for help with array and loop. I tried as Starx has written but I got nothing (white screen). I’m still learning so I know that I have to do something wrong.

............          .................       ..................
Table Thing           Table for field 4       Table for field 5

id int                id int                  id int
field 1 varchar       value varchar           value varchar
field 2 varchar
field 3 varchar       ................        ................
field 4 int
field 5 int
(...)
...........

Answer by Starx

StarxStarx

Whenever you are trying to access multidimensional array, you have to use indices to point a particular field.

For example:

$data['A']['B'][0]['C']['VALUE'] would give you the value some value1

Use a similar way to get the values and insert into the database.

...

Please fill the form - I will response as fast as I can!