Mysql in PHP runs one query and then failes on second,

Question

Ok Im sorry - I have been spending the last few days working on this, and Im stuck... and so I am asking for advice or fresh set of eyes to help me out here...

The code here has either been working or has been tested in the sql section of phpMyAdmin... some background, I have a db with two tables, users and roles. I am trying to insert a user into the users db (works 99% of the time...) and then after the user is created as long as there were no errors prior, then to create a row in the roles table and set the roles based on the user.... Seems simple but even though the queries work in the phpmyadmin and dont cause issue, the php code fails every time it is run... the user is created... and cannot access the rest of the site unless i manually add them to the roles table... Im sure that you can understand why I want to have this going when a user creates the login...

@ $query = "INSERT INTO users
(
  Username , Password , pwsecret , emailAddress , Fname , Lname , Joined_Date
)
VALUES
(
  '$uName' , '$encoded', '$salt' , '$email' , '$firstName' , '$lastName', CURRENT_TIMESTAMP
)";

ini_set('display_errors', 'On');

if (!mysqli_query($db,$query)){
$error_message = "There was a problem when attempting to add you as a user, please try again and if the issue persists please contact the webadmin.";
//clear vars for reuse::
$_POST = array(); //should stop data from persisting.
$uName = "";
$pw1 = "";
$pw2 = "";
$encoded = "";
$email = "";
$firstName = "";
$lastName = "";
include("partial/user/_register.php");
exit(); //jic something goes wrong we do not want to have the script resume.
}else{
//the following should add a newly generated user to the roles table.
$select="(SELECT userID FROM users WHERE Username = $uName)"; //sub query to return a user ID
$insert="INSERT INTO roles (userID , isAdmin , isMod , isFormerStaff , isUser , isBanned) VALUES ((SELECT userID FROM users WHERE Username = $uName;) , 0 , 0 , 0 , 1 , 0);"; //insert query to add user to roles table
//I tested this code in mysql phpmyAdmin and it worked with out error...
if (!mysqli_query($db,$insert)){
  echo "User role not created - please add user manually.";
}

In the second query there is a select statement in the userID field this was done since I need it to get the userID of the last person to register, Bear also in mind that this is being run right after the user is generated, and assume that the tables are clean.

Any help you guys can show or the source the issue would be greatly appreciated.... I know its probably something that I missed... but i cannot see it..

Jesse Fender

In multiple places, `Username = $uName;` <- remove the semi-colon and put quotes around `$uName` unless this is a number. Aside from that, you are highly susceptible to SQL injection. See http://stackoverflow.com/q/60174/2191572 and good luck — MonkeyZeus, Feb 07 '17 at 19:41
You are wide open for SQL injection. Since you're using mysqli, take advantage of [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [bind_param](http://php.net/manual/en/mysqli-stmt.bind-param.php). — aynber, Feb 07 '17 at 19:44
Instead of doing the select-inside-the-insert statement, you can [get the insert id](http://php.net/manual/en/mysqli.insert-id.php) of your first insert query. — aynber, Feb 07 '17 at 19:46
@MonkeyZeus - that got it working Thanks... I am aware that the code is not the most secure... I ahve been using prepared queries, but I wanted to make the code work first off... since this is a development server, the outside world has little or no access to it... +1 and thank you for taking your time it is greatly appreciated — Jesse Fender, Feb 07 '17 at 19:56
*"I need it to get the userID of the last person to register"* - so why not use `LAST_INSERT_ID()` instead? — Funk Forty Niner, Feb 07 '17 at 19:56
btw, that extra semi-colon wouldn't have thrown an error, since it's a valid character in php http://php.net/manual/en/language.basic-syntax.instruction-separation.php and it failed on you "silently". — Funk Forty Niner, Feb 07 '17 at 19:57
@aynber Thank you for your concern about this site and sql injection... please read my comment to MonkeyZeus - I generally use prepared statements but it was not returning errors and i was trying to get it fixed... I will be swapping back over in a bit... As for the second part, I was having issues with the return of id , so i tested it and it worked inside... so that what i was using Ill probably swap it back out though now that it is working. — Jesse Fender, Feb 07 '17 at 19:59
@Fred-ii- Last_Insert_ID(0 kept coming back as not a valid method i mysqli:: it wouldn't give the details and so i ditched the code for the select statement. As far as the an error being generated, I really wish it would while I am in a development server (wamp on windows), Im still learning PHP and so its wholly possible or more over probable that i am the cause of 100% of the errors and failings.... — Jesse Fender, Feb 07 '17 at 20:03
that's an mysql method; there's also an php method http://php.net/manual/en/mysqli.insert-id.php — Funk Forty Niner, Feb 07 '17 at 20:04
@Fred-ii- I have tried most every thing i could find in doc and on this site, and it hadn't been working... like i said, Iam pretty sure its something im doing though... — Jesse Fender, Feb 07 '17 at 20:05
`mysqli_insert_id()` example http://stackoverflow.com/a/15332270/1415724 - btw, is this question solved or not? — Funk Forty Niner, Feb 07 '17 at 20:27
Using prepared statements - Nice! I've delved into the `mysqli_*` documentation and even I have found that the library is confusing as a whole so keep trying and good luck! — MonkeyZeus, Feb 07 '17 at 20:48
@DeDevelopers. I did... It failed... Plus prepared statements help reduce the likelihood of sql injection. Since most of queries i am using elsewhere use prepared statements i just stuck with that format. — Jesse Fender, Feb 08 '17 at 11:30

score 0 · Answer 1 · answered Feb 07 '17 at 20:22

Jesse Fender,

I have seen that you have write select id statement, but its just a simply query in string format, you are not making it a query format by adding mysql_Query($select)

The code will work fine in mysql, coz there is no need to use mysql_query, as it acts like its a query, but from PHP you need to convert the string to query so it acts like a query..

Try this:

$select="(SELECT userID FROM users WHERE Username = $uName)";
$lasid = mysqli_query($db,$select); 

//sub query to return a user ID
$insert="INSERT INTO roles (userID , isAdmin , isMod , isFormerStaff , isUser , isBanned) VALUES ($lasid , 0 , 0 , 0 , 1 , 0);"; 
mysqli_query($db,$insert);
if (!mysqli_query($db,$insert)){
  echo "User role not created - please add user manually.";
}

`WHERE Username = $uName` - if that's (most likely) a string; it will fail. — Funk Forty Niner, Feb 07 '17 at 20:25

Mysql in PHP runs one query and then failes on second,

1 Answers1