0

I have several tables with many columns that will be populated from form data the user inputs in PHP. Instead of having to put each column name and value individually, INSERT INTOusers(email,password) VALUES ('$email','$password') is there a way to correctly do this using the json encoded data so with the more complex tables so I don't need to do:

INSERT INTO `profile`(`user_id`, `fName`, `lName`, `address1`, 
                    `address2`, `city`, `state`, `zip`, `phone`, 
                    `country`, `dob`, `homeBase`, `active`) 
            VALUES ('$user[id]','$fName', '$lName', '$address1', 
                    '$address2', '$city', '$state', '$zip', '$phone', 
                    '$country', '$dob', '$homeBase', 'active')`

where all the info comes from:

  $email = mysqli_real_escape_string($con, trim($request->data->email));
  $password = mysqli_real_escape_string($con, trim($request->data->password));
  $fName = mysqli_real_escape_string($con, trim($request->data->fName));
  $lName = mysqli_real_escape_string($con, trim($request->data->lName));
  $address1 = mysqli_real_escape_string($con, trim($request->data->address1));
  $address2 = mysqli_real_escape_string($con, trim($request->data->address2));
  $city = mysqli_real_escape_string($con, trim($request->data->city));
  $state = mysqli_real_escape_string($con, trim($request->data->state));
  $zip = mysqli_real_escape_string($con, trim($request->data->zip));
  $phone = mysqli_real_escape_string($con, trim($request->data->phone));
  $country = mysqli_real_escape_string($con, trim($request->data->country));
  $dob = mysqli_real_escape_string($con, trim($request->data->dob));
  $homeBase = mysqli_real_escape_string($con, trim($request->data->homeBase));

I am using Angular and Ionic to create the form and get the data, if that makes a difference to anyone. Everything works as is, just looking for a better way as the tables and forms grow.

Thanks in advance.

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Jim
  • 1,315
  • 4
  • 17
  • 45
  • 3
    ___You do need a better way___ Your script is open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) You should consider using [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's instead of concatenated values – RiggsFolly Apr 02 '20 at 14:57
  • @RiggsFolly this question has nothing to do with SQL injection. I am only posting the parts of the code that I felt were relevant. I have my security in place, this was asking for a better way to formulate the query to I was not assigning values for every column. Please re-open this as it was closed incorrectly! – Jim Apr 02 '20 at 15:09
  • 2
    If you are using that code, you do NOT have your security in place – RiggsFolly Apr 02 '20 at 15:12
  • 1
    "Instead of having to put each column name and value individually"...that's how you write SQL statements. There isn't a shortcut. (Well, technically you can leave out the column names, but that's well known as a bad practice, likely to cause maintenance / debugging / readability headaches later on). You could use the list in $request->data to try and auto-generate the SQL statement, but unless you know you'll always be inserting to all fields in your table, it could leave you vulnerable to over-posting attacks potentially. You'd have to validate carefully, which is always request-specific. – ADyson Apr 02 '20 at 15:13
  • If you want to write code to take some JSON and formulate a query out of it, thats fine, but there is no `magic_json_to_query()` function call if thats what you are asking – RiggsFolly Apr 02 '20 at 15:14
  • 2
    And the SQL injection comments above are perfectly relevant, IMHO. Security is always relevant. – ADyson Apr 02 '20 at 15:14

0 Answers0