GET and Update DB

I'm currently working on an iPhone app which passes data to a browser. Using this data, I need to update the database.

I'm having one of those duhhh days where I'm breaking pretty much everything ha!

I'll be updating a row by ID, but could be updating 1 or more fields. How can I tell what I need to update? I.e. if I try and update the whole row won't I wipe any empty fields?

I'm coding this in PHP

Would I need to use a case switch or is there a simpler method?

Thanks

 

Never mind, I was having a duh moment. I'll only be passing what I need to update.
==============
EDIT:
Nope back to duhhh.

If say, I have a table with:

- ID
- Name
- Location
- Time

And I want to update the row dynamically but maybe not all fields at once... How would I do it?

1 query may be:

$id=1&name=foo

Whereas another one may be:

$id=1&name=foo&time=10

 

If you update the row set name = 'glen' and time = '12312312312312' where id = 1; that's not going to affect the other columns in that row. I think that's what you was asking.

 

It's how would I do the query that I'm struggling with....

prepare("UPDATE tablename SET $_GET[???]");

PHP:

How do I know what to get? It could be 1 variable, it could be 3.

 

you could check to see if they are set and then add to the query?

$query = 'UPDATE `tablename` SET ';
$queryVariables = '';
if(isset($_GET['var1'])) {
$queryVariables .= " `var1` = '".$_GET['var1']."'";
}
$query = $query . $queryVariables . " where `id` = '1'";

Code (markup):

Something like that an repeat for the other variables. If you don't know how many are coming you can loop the $_GET global and add it that way.

 

You are working with a database and don't understand how to use UPDATE? I think you need to read a VERY simple database tutorial. INSERT, UPDATE, DELETE, SELECT are basic statements. You should know how to use each before you even write a lick of SQL.

As for passing information from a Browser or Mobile App to a Server the easiest way is with a basic GET or POST HTTP Request.

NEVER EVER directly use $_GET[] and $_POST[] inside your SQL statement. ALWAYS use Placeholders to avoid SQL Injection attacks. If you don't know what an SQL Injection attack is (you probably don't and neither does the guy who tried to help you) it's when a jerkoff sends your script an SQL statement that is then inserted into your SQL statement and processed. It leaves a HUGE security hole open allowing visitors to literally corrupt and destroy your database.

 

His using prepared statements. He don't need to worry about SQL injections. http://www.php.net/pdo.prepared-statements

 

^^ Probably still using mysql_query lol!

I've slept since yesterday! To put it a little better, if I attempt to grab all variables for that table, the update query would be something like:

$sth = $dbconn->prepare("UPDATE tablename SET field1 = :field1, field2 = :field2, field3 = :field3, field4 = :field4" WHERE id = :id);
$sth->execute(array(":field1" => ''.$_GET["field1"].'', ":field2" => ''.$_GET["field2"].'', ":field3" => ''.$_GET["field3"].'', ":field4" => ''.$_GET["field4"].'',":id" => ''.$_GET["id"].''));

PHP:

Now if the variable is blank, it will attempt to update that field anyway won't it? If it does, it will clear that field. The problem is, is how do I only update what is in the URL dynamically for url strings such as:

My current workaround is to send all data back from the iPhone APP to the browser and update it that way, but obviously with app and web development, I'm not wanting to send all that irrelevant data back as it's just a drain on resources and bandwidth, especially if the user is using 3G.

 

The original poster posted "prepare("UPDATE tablename SET $_GET[???]");" which is vulnerable to SQL Injections. Your advice of building out an SQL query is also vulnerable to SQL Injection. Just because you use PDO doesn't mean your queries aren't vulnerable to SQL Injections if you directly insert user defined variables. You must use placeholders to avoid SQL Injection.

 

Well if his using PDO and don't bind the values I doubt he would have bothered using prepare in the first place...

 

This isn't a debate. HE posted this code "prepare("UPDATE tablename SET $_GET[???]");" which is vulnerable. You posted "... $queryVariables .= " `var1` = '".$_GET['var1']."'"; ..." which is vulnerable. End of discussion.

 

Well I can see this argument will go on forever. But I do feel I should make my point a little clearer.

Doesn't matter if you use the global $_GET directly in an sql statement if it's prepared. I supplied him with some (sample) code how to put his variables together.

You sat there on your high horse giving a lecture about SQL injections when the actual subject of this topic was nothing about that AND you was wrong in your assumptions.

Let's end this here. We will agree to disagree.

 

I need to butt in here.
If you do this:

prepare("UPDATE tablename SET column = $_GET['variable']");

PHP:

it WILL BE vulnerable to SQL injection, regardless of the prepare-bit. Prepare does NOTHING unless you actually use the prepared statement, ala this:

prepare("UPDATE tablename SET column = :variable");

PHP:

If you use raw variables in the query, it doesn't matter what kind of potential security is available, as long as you don't actually use it.
I suggest you have a look at the comments-section here: http://www.php.net/manual/en/pdo.prepare.php

 

You are wrong again. If a variable is inserted DIRECTLY in to an SQL statement, the value of that variable becomes part of the statement. Inserting the SQL code directly in to the prepare statement with the user defined variable still leaves you prone to an SQL Injection attack. You look like a goof arguing this.

 

Thank you.

Unfortunately, we have a bunch of ignorant newbies who would rather be stubborn and wrong rather than to learn how to do it properly.

 

Who said anything about putting them in directly? He clearly showed he passed an array through execute which will bind the values. Why do people on these forums get so butt hurt about things? Sitting there in your mum's basement keyboard warrior's.

 

The first one I put in, I was extreeeemly tired and typed it out quickly. It was a mistake. Look at the actual query I put in, which is the correct way to do it.

This thread has gone way off topic, please keep it on topic and help answer my original question.

 

Your example and his original example.

 

It's not about being "butt hurt". It's about providing good advice, not just for the original poster but to anyone else who comes across this thread.