Updating an existing user does not work in php

Question

when I try to update my database to change the location of an existing user, I receive the text in an echo which is desired but the details of a user are not changed. Here is my code - server details have been removed on purpose for privacy.

{
     $server = ''; 
     $connectionInfo = array("Database"=>""); 
     $conn = sqlsrv_connect($server,$connectionInfo);
     if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
     } 
     echo "Connected successfully";
     date_default_timezone_set('Europe/London');
     $username = $_POST['username'];
     $location = $_POST['location'];
     $dateAndTime = date('d-m-y h:i a', time());
     $selection_query = "SELECT 1 FROM users WHERE username = '".$username."'";

     $result = sqlsrv_query($conn, $selection_query, array($username));
     if (sqlsrv_fetch_array($result) == 0) 
     {
     echo "Username does not exist.";
     }
     else
     {

     $updateUserQuery = "UPDATE users SET location='$location' datetime='$dateAndTime' where username='$username'";
     sqlsrv_query($conn, $updateUserQuery);

     echo $username;
     echo "'s location has been successfully updated to ";
     echo $location;
     echo " at ";
     echo $dateAndTime;
     echo ".";
     }

     sqlsrv_close($conn);

         }

Try adding sqlsrv_query($conn, $updateUserQuery) or die(sqlsrv_error()); — delboy1978uk, May 08 '17 at 18:43
[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)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, May 08 '17 at 18:45
`update location set location...`, something seems wrong about that, that is unless you actually have a table named `location` with a column named `location` inside of it. Yo dawg... — Jonathan Kuhn, May 08 '17 at 19:11
yeah i changed that now though it still doesn't edit information — heckerman, May 08 '17 at 19:31

Sari Rahal · Answer 1 · 2017-05-08T20:30:31.753

0

So looking at your code, you should check to make sure your connection exists before trying to do a query. Kinda like this:

// Create connection
$connectionInfo = array( "Database"=>"dbName");
$conn = sqlsrv_connect( $serverName, $connectionInfo);


if( $conn ) {
 echo "Connection established.<br />";
}else{
echo "Connected successfully";

This is important b/c it might not be that your query is returning no results but that you don't have a connection at all.

Once you have that, change this line in your query

$selection_query = "SELECT * FROM users WHERE username = '".$username."' LIMIT 1";

Also following what Jay Blanchard said in the comments, this way of writing querys leaves you open for SQL Inchections. This is a way for a user to write a query inside of your query that you wouldn't expect to run. Kinda like "DROP All Tables" query. Never good to allow. Following his link is a good idea.

edited May 08 '17 at 20:30

answered May 08 '17 at 18:47

Sari Rahal

1,897
2
32
53

when entering the connection check, i just got this as a response Notice: Trying to get property of non-object – heckerman May 08 '17 at 18:54
Can you update your code so i can see what you changed? – Sari Rahal May 08 '17 at 18:58
Yep I have done now, it's just after the $conn =sqlsrv_connect where I added it – heckerman May 08 '17 at 18:59
So what's the response you get? do you get "Connection failed: " and an error? or do you get "Connected successfully"? – Sari Rahal May 08 '17 at 19:02
No I dont think the if statement is working in my code as it just outputs connected successfully and acts as it previously was – heckerman May 08 '17 at 19:03
you never changed your $selection_query string. Take a look at my answer again for the correct one. You can also try an easy static query on your connection to see if the connection is working. That will narrow down your issue. – Sari Rahal May 08 '17 at 19:06
sorry i forgot to update that on here but I changed that string but it still doesn't change the database, though it is connected as it successfully checks the database if the username exists in the database – heckerman May 08 '17 at 19:09
Lol, so i change the query again. I added ticks to the table name and column name. This helps mysql know that it's referencing a table or column in case you're using reserved names. I believe user is one of them. And note, that they are ticks and not single quotes. – Sari Rahal May 08 '17 at 19:11
Look at question carefully, mysqli is not even tagged in the question this answer is blunty off – Masivuye Cokile May 08 '17 at 19:13
Sorry, I was searing you in the wrong direction. I fixed my answer. – Sari Rahal May 08 '17 at 19:17
I tried the fixed selection query though that just fails the program and can not find the user that i search for, though the connection checking confirms it is connected – heckerman May 08 '17 at 19:39
Try this: "SELECT * FROM `users` WHERE `username` = '".$username."' LIMIT 1" – Sari Rahal May 08 '17 at 20:29
Have you fixed your issue? – Sari Rahal May 11 '17 at 14:42

Updating an existing user does not work in php

1 Answers1