mysqli_real_escape_string alternative for decimal ?

[bambinou1980]

Hello,

 

I would like to add a bit of security to my script before sending the data to mysql using msqli.

 

At the moment I have:

$submit_product = mysqli_real_escape_string($connection, $submit_product);
$submit_price = mysqli_real_escape_string($connection, $submit_price);

But I am worried that the mysqli_real-escape_string is not right for the $submit_price" variable, what other laternative there are in PHP for decimal from $_POST?

 

Thank you,

 

 

Ben

Edited August 14, 2015 by bambinou1980

[Ch0cu3r]

 

But obviously mysqli_real-escape_string is not right for the $submit_price" variable, what other laternative there are in PHP for decimal from $_POST?

To validate that it is a float.

// validate price is a numeric value
if(is_numeric($_POST['price']))
{
   //typecast to a float
   $price = (float) $_POST['price'];
else
{
    // price is not numeric, fails validation
}

If the user provides a non numeric value, then it will fail validation.

 

mysqli_real_escape_string should only be used on string values (such as a persons name, contents to a blog post etc), that why it has string in its name, . It should not be used for non string values, such as numbers, floats etc. For those data types you should validate the data you are receiving is of that type/format you are expecting.

 

However if you are using mysqli, then do not use mysqli_real_escape_string instead use prepared statements.

Edited August 14, 2015 by Ch0cu3r

[mac_gyver]

if this is for your current project, where you have stated an admin is entering the order information, where you should be validating that the current visitor is logged in as an admin for both the form and the form processing code, why are you concerned about the security of the values (assuming that you are actually testing the current logged in user's permissions in the form processing code)?

 

anyway, a way of avoiding the need to validate the prices at all, would be to submit and store just the price number that was selected - 1,2,3, not the actual price, which if you don't trust your admin with, shouldn't be passed through the form (someone could change 100.00 to 1.00 for their friends.) if on the other hand, you are only submitting the price number, the only tampering with the price could be to select the wrong one among the choices for that product. which begs the question, of what happened to your concept of having a customer type that determines the price the customer gets for each product?

 

if you are accepting the actual price from external data, and just anyone can submit to your form processing code, and you don't care is someone supplies they own price (form data can be manipulated to be anything, not just what you output when you produced the form), you would want to treat the number as a decimal, not a float. casting/storing it as a float will introduce floating point conversion errors. also, using just is_numeric(), without other validation, will allow a hexadecimal number to be entered, which somewhere between php and mysql sadly has (unknown of this is still the case) converts to the encoded string and allows sql injection in the query statement.

 

i would also recommend prepared queries to provide security against sql injection for your external data as it works regardless of the data type and the value that was submitted.

Edited August 14, 2015 by mac_gyver

[bambinou1980]

Hi The reason is because next year when I will upgrade the script I will put it online and have the boss controlling the access from home.

 

I am left with one last problem, after this, everything will be fine as the hardest parts are not done.

 

I have 2 tables:

orders

 

order_status

 

 

When I have created my first insert, I have insert 0 in the orders row called orders_status_id.

 

Now upon select, I would like to select this "0" so it output the corresponding 0 value in the order_status page which is "Not Done".

 

How can I query the orders table in such way that it gives me the "Not Done" status please?

 

I am watching daily php and mysql videos trying to understand the whole process but on this relational part, I am a bit lost still.

 

Thank you,

 

Ben

[scootstah]

SELECT orders.*, order_status.status

FROM orders

JOIN order_status ON order_status.id = orders.order_status_id

You should be using auto increment on ID's though. And since you have a value of 0, that tells me that you are not doing that.

[Barand]

All that customer info should be in the customer table, not repeated in every order record. Only the customer_id should be in there so you can join to the customer table.

[bambinou1980]

SELECT orders.*, order_status.status
FROM orders
JOIN order_status ON order_status.id = orders.order_status_id

You should be using auto increment on ID's though. And since you have a value of 0, that tells me that you are not doing that.

 

Hi

Sccotash,

 

could you please let me know why use auto increment in that table if I have only 2 values? Would it make a bit difference to go from 0 and 1 to 1 and 2?

 

Thank you,

 

Ben

[bambinou1980]

All that customer info should be in the customer table, not repeated in every order record. Only the customer_id should be in there so you can join to the customer table.

Hi Barand,

 

 

Do you mean the order_status?

Why not having it in the order table itself? Would it not be easier?

 

Thank you,

 

Ben

[Barand]

I mean these fields

[Barand]

For your order status another option is to use an ENUM type field

CREATE TABLE IF NOT EXISTS `status_sample` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(40) DEFAULT NULL,
  `status` enum('Not Done','Produced') DEFAULT 'Not Done',
  PRIMARY KEY (`id`)
);

INSERT INTO status_sample (name, status) VALUES
('Peter', 1),
('Paul', 2),
('Mary', 1);

SELECT * FROM test.status_sample;
+----+-------+----------+
| id | name  | status   |
+----+-------+----------+
|  1 | Peter | Not Done |
|  2 | Paul  | Produced |
|  3 | Mary  | Not Done |
+----+-------+----------+

[scootstah]

Hi

Sccotash,

 

could you please let me know why use auto increment in that table if I have only 2 values? Would it make a bit difference to go from 0 and 1 to 1 and 2?

 

Thank you,

 

Ben

Because then you don't have to manage anything. You only have two statuses now, but you could have many more later on.

[bambinou1980]

Oh I see, ok got it, I thought there was something more behind it, no, those values will stay as it is(order processed or order not started or along those lines, only 2 choices..

 

Made great progress today with a lot of things, thanks to all!

Coding was like Chinese to me 2 weeks ago , now I am understanding the logic behind, of course it is a huge world and I will perhaps never know how to code like you guys but the most important for me is to now be able to create solutions based on my junior level :-)) I really enjoy it!

Sorry if I will ask a lot of different questions in the forum but each time I try a new thing, I need a bit of guidance or I might spend a lot of time trying to figure out something that is maybe super simple to you all.

 

Thanks again!

 

 

Ben

[scootstah]

Sorry if I will ask a lot of different questions in the forum but each time I try a new thing, I need a bit of guidance or I might spend a lot of time trying to figure out something that is maybe super simple to you all.

Not a problem man. As long as you do your due diligence and don't expect spoon feeding, we will be happy to help you along the way.