0

My question is regarding performance and script optimization. I'm fairly new to PHP and I have something like the following:

$Connection = new mysqli($Server, $DBUsername, $DBPassword, $DBName);
$Connection->query("UPDATE Basket SET Apples='$Apples', Oranges='$Oranges', Bananas='$Bananas' WHERE BasketName='$BasketName'"); 
$Connection->query("UPDATE Bag SET Napkins='$Napkins' WHERE BagName='$BagName'");
$Connection->query("UPDATE Drinks SET Water='$Water' WHERE DrinksName='$DrinksName'");

Is it ok that I have multiple $Connection->query, one for each table or is there a better way that's faster to write this?

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
stefanplc
  • 419
  • 9
  • 22
  • you can use a multi-query and while you're at it, use a prepared statement. You're open to an sql injection. – Funk Forty Niner May 02 '18 at 11:41
  • would you be so kind to show me an example of what you mean? – stefanplc May 02 '18 at 11:43
  • I'm getting my variables from the URL and I use the following to protect against sql injections: $BasketName = mysqli_real_escape_string($Connection, $_GET['BasketName']); Is that not enough? – stefanplc May 02 '18 at 11:49

1 Answers1

2

First of all, your code is vulnerable to a SQL injection. You should switch to prepared statements asap.

Using mysqli_real_escape_string is not enough to prevent SQL injections. See Is “mysqli_real_escape_string” enough to avoid SQL injection or other SQL attacks?

An example of a prepared statement:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// prepare and bind
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();

To answer your original question. You could use mysqli::multi_query. I personally find it a lot cleaner if the queries are split up query per query.

ThomasVdBerge
  • 7,483
  • 4
  • 44
  • 62