Does php get updated id (auto_increment) in the same connection after an insert or do you have to reconnect?

Question

I have the php:

$con=mysqli_connect("","","","");


$sql="INSERT INTO xxx (xxxx, yyyy, zzzz)
VALUES
('$_POST[A]','$B','$C')";


$result = mysqli_query($con,"SELECT id FROM xxx ORDER BY id DESC");

$row = mysqli_fetch_array($result);

Will this $id be the one before the inserted data or after?

$id = $row['id'];
mysqli_close($con);

You are **wide open** to SQL injection attacks and **you will be hacked** if you haven't been already. Use prepared/parameterized queries to avoid this problem entirely. — Brad, Feb 20 '14 at 03:49
That doesn't change the fact that you are wide open to attack. You are fundamentally querying your database incorrectly. I'm letting you know before you go down the road of building an entire application this way, leaving you out in the open. It's not as if someone has to manually target your site... there are automated bots very good at finding injection attacks these days. Within hours of putting up code like this, you can bet something will find it. — Brad, Feb 20 '14 at 03:51
OK so I have used variables in statements like that for months and IM fine, how should I do this properly to avoid future attacks — Hurricane Development, Feb 20 '14 at 03:54
Well you have official scared the s**t out of me! I do love hackers! — maxisme, Feb 20 '14 at 03:54
@LagMaster I have just been taught about it. Bare with me I am really bad at explaining. So I will just give you a basic example. If you have a form say. And you are submitting text that the user puts in the form to your database. You have to do something like this:`INSERT INTO foo (bar) VALUES ('$_POST[bar]')`. If the user then puts something like like this: `ABC')` into the form they can close what they are inserting. So then they can add more code like another mysql query into the php where they can add or view anything on your database. I hope that explains it okay. — maxisme, Feb 20 '14 at 08:20
What I would do to prevent it is to before the `'$_POST[bar]'` is inserted, strip the user from being able to put characters like `'` and `)` into the form @Brad Is that an okay explanation? — maxisme, Feb 20 '14 at 08:21
by doing something like this: `stripslashes(htmlspecialchars($_POST['bar']));` — maxisme, Feb 20 '14 at 08:27
@LagMaster I am worried that my last comment is utter rubbish. So don't use it. I think it probably just helps a little bit but is not 100% secure. Try [this](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — maxisme, Feb 20 '14 at 12:08
I wish that it was an official question! Haha I have never answered one before! — maxisme, Feb 20 '14 at 12:09

score 0 · Accepted Answer · answered Feb 20 '14 at 03:51

0

Auto-increments work regardless of connection. If you had 1,000 clients connected all inserting simultaneously, the auto-increment ID is guaranteed to be incremented as you would expect. It's all nice and atomic like that.

answered Feb 20 '14 at 03:51

Brad

159,648
54
349
530

I think you have misunderstood my question. I have just tested it and if I put `echo $id;` before `mysqli_close($con);` It will echo the id of the id before the php page was 'opened' – maxisme Feb 20 '14 at 06:03
I needed to put this `if (!mysqli_query($con,$sql)) { die('Error: ' . mysqli_error($con)); }` before `$id = $row['id']; mysqli_close($con); php` – maxisme Feb 20 '14 at 06:08

Hurricane Development · Answer 2 · 2014-07-19T03:19:53.523

0

It will be the one after as you are retrieving it after, no reconnect needed.

PLEASE NOTE: if another client updates the information then the results will be incorrect.

edited Jul 19 '14 at 03:19

answered Feb 20 '14 at 03:53

Hurricane Development

2,449
1
19
40

This isn't true. Other clients could insert before the select is finished. – Brad Feb 20 '14 at 05:04
Ok but thats not what his question is about. The question has nothing todo with other clients – Hurricane Development Feb 20 '14 at 11:37
If an answer contains wrong information it should be downvoted. Fix your answer, I'll remove the downvote. – Brad Feb 20 '14 at 14:44
@Brad finally fixed it I believe – Hurricane Development Jul 19 '14 at 03:20

score 0 · Answer 3 · answered Feb 20 '14 at 04:00

0

Php provides a last insert id function. The auto increment id created is sent in the response packet from mysql so no need to query for the last id like you are doing.

See http://us3.php.net/manual/en/mysqli.insert-id.php

answered Feb 20 '14 at 04:00

Daniel Williams

8,673
4
36
47

I am posting it into another table that is why I am querying it! – maxisme Feb 20 '14 at 04:02
You mean copying the id over? What does posting to another table mean? Also your example shows inserting into one table then querying that table. – Daniel Williams Feb 20 '14 at 04:05
Sorry do you know id this will work? `$resulty = mysqli_query($con,"SELECT max(idnumber) FROM users WHERE username =='$name'");` – maxisme Feb 20 '14 at 04:24
If you have duplicate usernames that is not guaranteed to work but it is likely to work for you is my guess – Daniel Williams Feb 20 '14 at 04:33
@Maximilian You can't guarantee that the largest ID is the one you just inserted. You must check for the last inserted id, and by this connection. – Brad Feb 20 '14 at 05:04
I am getting this error! Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given – maxisme Feb 20 '14 at 05:35

score 0 · Answer 4 · answered Feb 20 '14 at 05:51

0

try using below code

$qry= "SELECT LAST_INSERT_ID() AS insert_id";

$c = mysqli_fetch_assoc($qry);
echo $c['insert_id'];

answered Feb 20 '14 at 05:51

Nishant Solanki

2,119
3
19
32

Does php get updated id (auto_increment) in the same connection after an insert or do you have to reconnect?

4 Answers4