0

I am trying to copy the contents of a table from one server to a duplicate table on another server. This will be in a cronjob. There are only about 200 records that need daily updating. I don't want to use PHPMYAdmin, Navicat, etc. as I want it to be run via the server. I am truncating the destination table prior to the copy.

I was following the example here: https://stackoverflow.com/a/23657043/2413654 ($L1 is the connection to server 1, $L2 for server 2)

But some of the data in source table contains apostrophes, etc. and the insert fails.

I tried adding mysqli_real_escape_string to the value of $v in the iteration below, but it still errors on the first apostrophe encountered in the data.

Any suggestions? Is there a better way of doing this task?

Here is my code:

$re = mysqli_query($L1, "SELECT * FROM ft_form_2");
$keyfield = 'submission_id';

while($i=mysqli_fetch_assoc($re))

{
    $u = array();
        foreach($i as $k=>$v) 

            $v = mysqli_real_escape_string($L2,$v); // added, but not working

    if($k!=$keyfield) $u[] = "$k='$v'";
    mysqli_query($L2,"INSERT INTO ft_form_2_test (".implode(',',array_keys($i)).") 
    VALUES ('".implode("','",$i)."') ON DUPLICATE KEY UPDATE ".implode(',', $u)) 
        or die(mysqli_error());
}
WGS
  • 199
  • 4
  • 17
  • 1
    That example is terribly outdated and teaches bad habits. Use prepared statements with bound parameters, via either [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php). [**This post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has some good examples. – Alex Howansky Aug 01 '18 at 18:05
  • 1
    If you always want to wipe and re-create the table on the destination server, then you're probably better off just using mysqldump. – Alex Howansky Aug 01 '18 at 18:07
  • 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 manual escaping and string interpolation or concatenation to accomplish this because you will create severe [SQL injection bugs](http://bobby-tables.com/). Accidentally unescaped data is a serious risk. Using bound parameters is less verbose and easier to review to check you’re doing it properly. – tadman Aug 01 '18 at 18:10
  • There's zero reason to use this PHP code. You can do this as a `INSERT INTO ... SELECT ... FROM` style query and avoid the nightmare that is that `mysqli_query` call. Everything about this code is **extremely concerning**. – tadman Aug 01 '18 at 18:12
  • 1
    Another option is to dump as CSV on one server and `LOAD DATA INFILE` on the other. This is often faster than shuttling over from one connection to another. – tadman Aug 01 '18 at 18:24
  • why are you using `$L2` as the connection here for `mysqli_real_escape_string()`? Your query is using `$L1`. – Funk Forty Niner Aug 01 '18 at 18:25
  • `mysqli_error()` requires a db connection as an argument here. – Funk Forty Niner Aug 01 '18 at 18:26
  • *"$L1 is the connection to server 1, $L2 for server 2"* - How do we know if you haven't been disconnected somewhere? This post is unclear for me. – Funk Forty Niner Aug 01 '18 at 18:27
  • 1
    Thanks for all your input. I will study up on PDO. Went with @tadman with a CSV dump and LOAD DATA INFILE until I properly code it in PDO. – WGS Aug 01 '18 at 18:54

0 Answers0