-1

This should be pretty simple, I am just new to PHP... If I have this.. http://localhost/write_data.php?value=99 and inside the write_data.php is this...

$dbconnect = mysqli_connect($server, $dbusername, $dbpassword);
$dbselect = mysqli_select_db($dbconnect, "plantData_001");

// Prepare the SQL statement

$sqlAddress = "INSERT INTO sensorData (value) VALUES ('".$_GET["value"]."')";    

// Execute SQL statement

mysqli_query($dbconnect, $sqlAddress);

everything works fine and 99 is appended into the data table. But how do I add multiple values? I have a column named id that needs to be called like this... http://localhost/write_data.php?id=1234567;value=99

ΔO 'delta zero'
  • 3,506
  • 1
  • 19
  • 31
Justin Oberle
  • 502
  • 3
  • 22
  • The statement is `INSERT INTO sensorData (value) VALUES (99), (100), (101);` – ΔO 'delta zero' Nov 24 '20 at 23:04
  • But you should never insert unescaped strings directly into a sql statement, as it's vulnerable to sql injection attacks. Use [mysqli_real_escape_string](https://www.php.net/manual/en/mysqli.real-escape-string.php) or rather some smart DB layer, like [dibi](https://dibiphp.com/en/) to handle it (and much more) for you. – ΔO 'delta zero' Nov 24 '20 at 23:05
  • the insert is happening when I do this http://localhost/write_data.php?id=1234567;value=99. I am not using insert in sql directly. It gets the values from localhost. Also, I am not trying to insert multiple values for the same column. I want to insert 1 value for 2 different columns. – Justin Oberle Nov 24 '20 at 23:06
  • Ah I see. The statement for inserting 1 row of multiple columns is `INSERT INTO sensorData (id, value) VALUES (1234567, 99);` – ΔO 'delta zero' Nov 24 '20 at 23:08
  • I tried this but it puts everything in the ip column... $sqlAddress = "INSERT INTO plantData_001.sensorData (ip, value) VALUES ('".$_GET["ip"]."', '".$_GET["value"]."')"; – Justin Oberle Nov 24 '20 at 23:10
  • That code should be working, let us have a look on parsed $sqlAddress value. – ΔO 'delta zero' Nov 24 '20 at 23:13
  • Can you elaborate? I am not sure what you need from me. – Justin Oberle Nov 24 '20 at 23:16
  • `echo $sqlAddress;` and show us what it gives you. – ΔO 'delta zero' Nov 24 '20 at 23:16
  • Where do I enter this? – Justin Oberle Nov 24 '20 at 23:17
  • Just before mysqli_query command. – ΔO 'delta zero' Nov 24 '20 at 23:18
  • here is the result in browser..INSERT INTO plantData_001.sensorData (ip, value) VALUES ('192.168.1.140;value=99', '') – Justin Oberle Nov 24 '20 at 23:19
  • 2
    Ah I see... you've got it all in `$_GET['ip']` because your URL format is wrong. Use: `http://localhost/write_data.php?id=1234567&value=99` (that's `&value` instead of `;value`). – ΔO 'delta zero' Nov 24 '20 at 23:21
  • Perfection! Than you. That was exactly the solution! – Justin Oberle Nov 24 '20 at 23:22
  • 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 Nov 24 '20 at 23:28
  • 1
    @ΔO'deltazero' Manual escaping is a last resort. dibi might be useful, but PDO is built-in and works fairly well in a basic capacity. – tadman Nov 24 '20 at 23:28
  • 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 Nov 24 '20 at 23:29
  • Glad to hear that :-) PS: you really should escape your inputs. Just imagine someone would call something like `http://localhost/write_data.php?id=1234567&value=%27%20OR%20%28DROP%20TABLE%20sensorData%29%20OR%20%27` – ΔO 'delta zero' Nov 24 '20 at 23:29
  • @ΔO'deltazero' Please, stop telling people to escape input. They should use parameter binding. I know you mean well, but please stop spreading incorrect information. – Dharman Nov 24 '20 at 23:41
  • @Dharman, yeah, you're right. I meant it as 'at least' sort of solution, I totally agree parameter binding is the correct approach. – ΔO 'delta zero' Nov 25 '20 at 01:36

1 Answers1

0

Do you want to insert multiple values? You should accept the new way to pass values by script query param.

Something like that: http://localhost/write_data.php?values=99,100,88,20,44.

Then you should use i.e. explode function to split values into array and would be able to insert multiple rows.

BUT - you should implement also the functionality to secure your input from query param. Please, read about SQL injection and MySQLi - https://stackoverflow.com/a/16282269/8890700