duplicate INSERT entries in mysql

Question

I have a problem with an application in PHP. The problem is that it makes a few records in the database. Where is the problem?

  $sql = "INSERT INTO user_list (ip,verified,rank)VALUES ('$ip','$verified','$rank')";
            $mysqli->query($sql);

Thank you

The code shown will insert (or at least attempt to) exactly one record. (Unless, of course, the SQL injection vulnerability is being used to insert more records.) — David, Feb 20 '17 at 20:51
you sure you're not running this on a function or querying twice? Could be something else also, but the Goblins will never tell (their secrets). — Funk Forty Niner, Feb 20 '17 at 20:55
My function works with API and application copies the data in MySQL, but several copies same things and ID are correct. What caused this? — bdev, Feb 20 '17 at 21:01
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) — Jay Blanchard, Feb 20 '17 at 21:18

RomanPerekhrest · Accepted Answer · 2017-02-20T21:12:15.877

The right way:

1) preparing and binding values with mysqli_stmt::prepare and mysqli_stmt::bind_param functions:

// connection object instantiating
$mysqli = new mysqli(......);
...
$stmt = $mysqli->prepare("INSERT IGNORE INTO user_list (ip, verified, rank) VALUES (?, ?, ?)";
$stmt->bindparam('sii', $id, $verified, $rank);
$stmt->execute();
...

2) using INSERT ... IGNORE OR INSERT ... ON DUPLICATE KEY UPDATE syntax to avoid/ignore duplicate inserts

duplicate INSERT entries in mysql

1 Answers1