1

ok so I have written some code that is doing a backup of configuration items. This script runs fine for everything else, but fails when it gets to this long query for this specific configuration item.

for sanity purposes I am just going to include the code for this section.

function writepool($pool, $device){
  $pooltext = implode('', $pool['list']);
  $sql = "INSERT into pools (deviceid, name, config) VALUES (
  '".$device."',
  '".$pool['pool']."',
  '".addslashes($pooltext)."'
  )";
  echo $sql;
  $addpool = insertdb($sql);
}

function insertdb($sql){
  include('/var/www/db_login.php');
  $conn = new mysqli($db_host, $db_username, $db_password, "F5");

  // check connection
  if ($conn->connect_error) {
    trigger_error('Database connection failed: '  . $conn->connect_error, E_USER_ERROR);
  }

  if($conn->query($sql) === false) {
      trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);
  } else {
      $last_inserted_id = $conn->insert_id;
      $affected_rows = $conn->affected_rows;
  }
      $result["lastid"] = $last_inserted_id;
      $result["affectedrow"] = $affected_rows;

      return $result;
      $conn->close();
}

The error message I get is as follows

Fatal error: Wrong SQL: INSERT into pools (deviceid, name, config) VALUES ( '71', 'shopping.a.prod_pool_53601', 'ltm pool /Common/shopping.a.prod_pool_53601 { load-balancing-mode weighted-least-connections-node members { /Common/10.216.26.55:53601 { address 10.216.26.55 priority-group 1 } /Common/10.216.26.57:53601 { address 10.216.26.57 priority-group 1 } /Common/10.216.26.58:53601 { address 10.216.26.58 priority-group 1 } /Common/10.216.26.59:53601 { address 10.216.26.59 priority-group 1 } /Common/10.216.26.60:53601 { address 10.216.26.60 priority-group 1 } /Common/10.216.26.61:53601 { address 10.216.26.61 priority-group 1 } /Common/10.216.26.62:53601 { address 10.216.26.62 priority-group 1 } /Common/10.216.26.66:53601 { in /var/www/html/functions.php on line 2286

Note the query is extremely long. This particular configuration item is huge. I am storing this inside a BLOB in MYSQL.

If I echo the $sql variable I can see my entire query string. The query is too long to place here.

'If I copy the query string to MYSQL it works. Also if I copy the query string from my echo. and use a test page and put $sql= to the string echo'd by my failed script. it works. I wish I could post the query but it is too long due to blob data.

****** UPDATE *********

I did what tadman suggested, I moved to a prepared statement. However, now I am not getting any data input into the blob in my table.

function writepool($pool, $device){
$pooltext = implode('', $pool['list']);
/*
$sql = "INSERT into pools (deviceid, name, config) VALUES (
'".$device."',
'".$pool['pool']."',
'".addslashes($pooltext)."'
)";
*/
#$addpool = insertdb($sql);

include('/var/www/db_login.php');
$mysqli = new mysqli($db_host, $db_username, $db_password, "F5");

// Check connection
if($mysqli === false){
    die("ERROR: Could not connect. " . $mysqli->connect_error);
}

// Prepare an insert statement
$sql = "INSERT into pools (deviceid, name, config) VALUES (?, ?, ?)";

if($stmt = $mysqli->prepare($sql)){
    // Bind variables to the prepared statement as parameters
    $stmt->bind_param("ssb", $db_device, $db_pool, $db_text);

    // Set parameters
    $db_device = $device;
    $db_pool = $pool['pool'];
    $db_text = addslashes($pooltext);

    // Attempt to execute the prepared statement
    if($stmt->execute()){
        #echo "Records inserted successfully.";
    } else{
        echo "ERROR: Could not execute query: $sql. " . $mysqli->error;
    }
} else{
    echo "ERROR: Could not prepare query: $sql. " . $mysqli->error;
}

// Close statement
$stmt->close();

// Close connection
$mysqli->close();
}

$db_text is a section of configuration data generated by a system file. I was storing this as a blob since it is huge (1600+ lines long).

Tony Haynes
  • 69
  • 1
  • 7
  • 1
    **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 user 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 **any** user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. – tadman Aug 23 '17 at 22:48
  • Do try and get out of the habit of cluttering up your code with needless things like `=== false`. Many functions are designed to return values that evaluate as logically true or false so that's redundant. – tadman Aug 23 '17 at 22:48
  • seeing that the input to this code is a system generated file and not user data, I don't see where $_POST and $_GET data come into play here nor are those globals even used in the code I posted. – Tony Haynes Aug 23 '17 at 23:05
  • 1
    You're using `addslashes` which is a huge red flag. Just use **prepared statements with placeholder values** and don't half-ass it. You will get burned if you insist on doing this. You're clearly worried about irregular characters in `$pooltext` so it needs to be escaped. Do it properly and you'll never have problems. – tadman Aug 23 '17 at 23:07
  • 1
    This code also creates a new SQL connection for each query which is extremely inefficient. Normally you create one connection, use it, and push it back into a pool when done. – tadman Aug 23 '17 at 23:10
  • 1
    I am using addslashes to prevent errors when it's reading in the configuratino items which has some characters that need escaped. I will attempt to convert over to the prepare method. I have never used it so I will have to test. – Tony Haynes Aug 24 '17 at 00:01
  • hmmm ... PDO has a maximum buffer size (see **[here](https://stackoverflow.com/questions/24781930/mysql-pdo-maximum-longblob-data-length-on-fetch)** . Just guessing, but maybe mysqli has a similar limit and setting. – YvesLeBorg Aug 24 '17 at 02:57
  • updated my code but the blob data isn't getting stored in the DB table. not sure why. None of the blob data is being saved it seems now when before smaller sized blobs were being saved just fine. – Tony Haynes Aug 24 '17 at 17:56

0 Answers0