0

I am doing php and writing some code to insert data into different tables at the same time. I wrote a foreach loop and one part of my code is as follows:

 while ($datarow = dbgetrow($dataresult)) {
                    if ( $dotcount > $datanumrows) {
                            showarchivestatus(" .", true);
                            $dotcount = 1;
                    }
                    $sqlvalues = "";
ccy
  • 341
  • 6
  • 18
  • more code: $sqlvalues = ""; foreach($lu_cols as $lu_col) { if ($datarow[$lu_col] == "") { $sqlvalues = $sqlvalues.", NULL "; } else { $sqlvalues = $sqlvalues.", \"".makesafe($datarow[$lu_col])."\""; } } – ccy Feb 26 '16 at 20:35
  • Add this into question – devpro Feb 26 '16 at 20:36
  • You should use prepared queries, then you won't run into problems like this. – Barmar Feb 26 '16 at 20:39
  • If not, you need to escape your strings. That needs to be done in the code that creates `$sqlvalues`. – Barmar Feb 26 '16 at 20:39
  • 1
    @devpro `htmlentities` is for encoding strings in HTML, it has nothing to do with databases. – Barmar Feb 26 '16 at 20:40
  • @Kyan Add that code to the question, not a comment. The problem is apparently in `makesafe`, it's not escaping backslashes properly. – Barmar Feb 26 '16 at 20:41
  • @barmar escape the string is good... And one more thing OP using connection inside the function – devpro Feb 26 '16 at 20:42
  • @Barmar@devpro Thank you, I will add more code and post the makesafe function – ccy Feb 26 '16 at 20:54

1 Answers1

2

You need to escape your string before putting it into the database.

Here is a basic example of how to do it in MySQLi

<?php
$con=mysqli_connect("localhost","my_user","my_password","my_db");

// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// escape variables for security
$firstname = mysqli_real_escape_string($con, $_POST['firstname']);
$lastname = mysqli_real_escape_string($con, $_POST['lastname']);
$age = mysqli_real_escape_string($con, $_POST['age']);

$sql="INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('$firstname', '$lastname', '$age')";

if (!mysqli_query($con,$sql)) {
  die('Error: ' . mysqli_error($con));
}
echo "1 record added";

   mysqli_close($con);
?>

Here is an example of PDO:

<?php
$conn = new PDO('sqlite:/home/lynn/music.sql3');

/* Dangerous string */
$string = 'Naughty \' string';
print "Unquoted string: $string\n";
print "Quoted string:" . $conn->quote($string) . "\n";
?>

You may want to consider using a prepared statement. There are several benefits to this including:

  1. Security - Helps prevent SQL injection
  2. Speed - You only are sending the values.

http://www.w3schools.com/php/php_mysql_prepared_statements.asp

Sources:

http://www.w3schools.com/php/func_mysqli_real_escape_string.asp

http://php.net/manual/en/mysqli.real-escape-string.php

http://php.net/manual/en/pdo.quote.php

Cayde 6
  • 617
  • 6
  • 19
  • thank you, but I am thinking about the problem is apparently in makesafe, it's not escaping backslashes properly, which @Barmar mentioned above. I am trying to use $text = str_replace("\", "\\", $text); – ccy Feb 26 '16 at 21:08