0

So I have a NodeMCU programmed to send float data to a hosted MySQL Database. I made it so that the value being sent is printed in the serial monitor for me to make sure that it's correct. Up until this point, everything works fine.

However, when I check the recorded values, it would only have '1.00'. For example, the supposed recorded value according to the serial monitor should be '12.65', but the database would have '1.00' instead. I tried converting the variable type to float using (float) $var and floatval($var) but it still had the same result. What am I missing?

    if ($_SERVER["REQUEST_METHOD"] == "POST")
    {
        $api_key = test_input(isset($_POST["api_key"]));

        if ($api_key = $api_key_value)
        {
            $var = test_input(isset($_POST["DB_Column"]));

            $insert_var = mysqli_query($conn, "INSERT INTO SCHEMA.Table(Time, Record)
                                VALUES(CURRENT_TIMESTAMP, '" . $var . "')
                                ;");

            if (!$insert_var)
            {
                die("Query failed: " . mysqli_error($conn));
            } 
        }
    }

    function test_input($data) 
    {
        $data = trim($data);
        $data = stripslashes($data);
        $data = htmlspecialchars($data);
        return $data;
    }
coding_is_hard
  • 87
  • 1
  • 9
  • 3
    **WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add any data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or data *of any kind* directly into a query, it can be very harmful if someone seeks to exploit your mistake. – tadman Apr 08 '20 at 18:06
  • Note: The [object-oriented interface to `mysqli`](https://www.php.net/manual/en/mysqli.quickstart.connections.php) is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface where missing a single `i` can cause trouble. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era and should not be used in new code. – tadman Apr 08 '20 at 18:06
  • 3
    Please, for your own safety, take `test_input` and throw it in the trash. Never, ever use it again. This thing damages data and provides *zero* protection against SQL injection. Code like that has been floating around for far too long and it needs to be expunged from the PHP ecosystem. – tadman Apr 08 '20 at 18:07
  • Thanks for this. I had a hunch that ```test_input``` does nothing significant but still used it anyways since the tutorial I was seeing had it. I will revise my code in accordance to your advice. Thank you very much. – coding_is_hard Apr 08 '20 at 18:52
  • If your tutorial recommends using that then that tutorial is automatically not worth using and will only damage you by teaching extraordinarily bad habits. There is better material out there. – tadman Apr 08 '20 at 19:51
  • 1
    If you're just getting started with PHP and want to build applications, I'd strongly recommend looking at various [development frameworks](https://www.cloudways.com/blog/best-php-frameworks/) to see if you can find one that fits your style and needs. They come in various flavors from lightweight like [Fat-Free Framework](https://fatfreeframework.com/) to far more comprehensive like [Laravel](http://laravel.com/). These give you concrete examples to work from and guidance on how to write your code and organize your project's files. – tadman Apr 08 '20 at 19:51

1 Answers1

2

In both cases you are actually getting the values from isset() which returns true or false.

$var = test_input(isset($_POST["DB_Column"]));

If you want to make sure there is a value then usually you would add it to your inital test...

if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST["DB_Column"])...

or to give it a default value, use ??, in here it defaults to 0...

$var = test_input( $_POST["DB_Column"] ?? 0 );

Also rather than using test_input(), you should be using prepared statements to protect from SQL injection etc, How can I prevent SQL injection in PHP?.

Nigel Ren
  • 56,122
  • 11
  • 43
  • 55
  • I tried doing your first example and it works properly. So basically, the value I get is "1.00" because ```isset()``` returned true right? I also tried studying and applying prepared statements. Thanks for your insight. – coding_is_hard Apr 08 '20 at 18:55