PHP & MySQL - problem with quoting

[Freston]

I'm pretty new at PHP and MySQL. But I'm writing a piece of PHP that must iterate through an array of items and count how many times these items are in a MySQL table.


It goes a little bit like this:

$list = array( item1, item2, item3 );

foreach ( $list as $current )
{
   $query[foo] = "select * from database.table
                  where bar = $current";     //$current is the problem
   $row = mysql_query($query[foo]) or die ("0h no");
   $interesting = mysql_num_rows($row);
   print "$interesting";
}

This however does work:

$list = array( item1, item2, item3 );

foreach ( $list as $current )
{
   $query[foo] = "select * from database.table
                  where bar = 'item1' ";     //$current is the problem
   $row = mysql_query($query[foo]) or die ("0h no");
   $interesting = mysql_num_rows($row);
   print "$interesting";
}

The problem is with the quotes. While selecting, I need to quote the value as 'value'... but when it's a $variable, the quotes prevent the expanding of the '$variable'. I tried escaping them, but that doesn't seem to work either. And using double quotes for "$variable" only makes it worst. Is there a trick to this?


The reason I do this, is because I don't know in advance how many items end up in the $list array. It is itself a product of another MySQL query.

[elija]

When I set up a little test and output your SQL from the first example I get

select * from database.table where bar = item1
select * from database.table where bar = item2
select * from database.table where bar = item3

Assuming that item1, item2 and item3 are values of bar and not fields, they should be in single quotes as they are strings. Try this instead:

 $query[foo] = "select * from database.table
                  where bar = '$current'";

Or indeed this, which is actually better practice as it delimits the variable.

 $query[foo] = "select * from database.table
                  where bar ='{$current}'";

You should be able to get the same results with one database query. Consider:

SELECT bar, COUNT(*) AS howMany FROM Foo WHERE bar IN('item1', 'item2', 'item3') GROUP BY bar

Hope this helps

[elija]

In fact

$query = "SELECT bar, COUNT(*) AS howMany FROM Foo WHERE bar IN(";
foreach ($list as $current) {
	$query .= "'{$current}', ";
}
$query = trim($query, ', ');
$query .= ") GROUP BY bar";

[Freston]

Thanks elija!

This was the trick:

$current = trim($current};
$query[foo] = "select * from database.table
                  where bar = '{$current}';

The problem was with the quoting, but also some unwanted whitespace was in the values of my array. That's two ' problems' for the price of one

[Kloschüssel]

(banging head)

sorry, if I am going to be rude. don't take it personally.

PLEASE don't post stupid things that make things only worse. this solution actually compromises the server security because one can sql inject your query. it shows once again that most people do things without the slightest idea or interest of the matter. i dislike saying that, but YOU are one source why most web pages are seriously vulnerable because of server misconfiguration, bad programming and lack of other countermeasures. please, LEARN more of software engineering, one might misunderstand YOU being a software engineer without the actual engineering thing and others that are software engineers that comply to the engineering part, but are regarded as retards because of people working like you do.

now back to topic. if you don't know how to SAFELY quote your arguments yet, I strongly recommend:

1] understand/learn what I am talking about
2] use the routines offered by mysql. if you don't know that they exist, look it up. you should already know the right resources where to find this kind information (likewise a documentation/manual - in this case the mysql one)
3] start thinking when you are doing things

[elija]

(banging head)

sorry, if I am going to be rude. don't take it personally.

PLEASE don't post stupid things that make things only worse. this solution actually compromises the server security because one can sql inject your query. it shows once again that most people do things without the slightest idea or interest of the matter. i dislike saying that, but YOU are one source why most web pages are seriously vulnerable because of server misconfiguration, bad programming and lack of other countermeasures. please, LEARN more of software engineering, one might misunderstand YOU being a software engineer without the actual engineering thing and others that are software engineers that comply to the engineering part, but are regarded as retards because of people working like you do.

now back to topic. if you don't know how to SAFELY quote your arguments yet, I strongly recommend:

1] understand/learn what I am talking about
2] use the routines offered by mysql. if you don't know that they exist, look it up. you should already know the right resources where to find this kind information (likewise a documentation/manual - in this case the mysql one)
3] start thinking when you are doing things

Wow. I really wish I had your brain power and especially your psychic ability. It's a really cool super power being able to tell everything about code you haven't seen from an example snippet. It would make my day job so much easier if I could do that.

Of course if data is coming from an untrusted source it should be escaped, used in prepared statements or if you are using MySQL 5, preferably moved into stored procedures.

Benefit me with your wisdom O psychic one, if a hard coded array isn't a trusted source, what is?

Freston, I will add here that data from a database, even your own database, especially one where more than one coder has worked on it and especially one that has data in it is not a trusted source.

[Freston]

@Kloschüssel
Hold your horses mate - false alarm

I agree with every point you make, it's completely valid. But this is on my private lappy, and at no point in the code does it ask for user input. I have no pretence this part of the code will ever be internetfähig.

bad programming

$statement  = true;

Don't let my 'Linux engineer' title here on LFo fool you, I'm by no means a good programmer. But I am practicing getting data from a database using PHP, yes.

Freston, I will add here that data from a database, even your own database, especially one where more than one coder has worked on it and especially one that has data in it is not a trusted source.

Most things I write are just finger practice. It'll be a while before I can do something productive. I'm sure to stop and have a look at the security secion of my books before then. I promice

___
Posted without the benefit of a spell chekcer

[Lakshmipathi]

writing a piece of PHP that must iterate through an array of items and count how many times these items are in a MySQL table.

Originally Posted by Kloschüssel
this solution actually compromises the server security because one can sql inject your query.

Unless he is taking input from users there is no need to worry about SQL injection.
SQL injection - Wikipedia, the free encyclopedia

[Kloschüssel]

Unsafe code is always unsafe code, whether it will be executed by attackers or not. In the end you quite fast loose the overview and may get in the situation where previously "safe code because of a static array as parameter" becomes a argument from the outside world, wrapped through N function calls or you copy it into a productive environment because you have written it once and it worked pretty well back then. Oh, and there may be also that others search up exactly this problem, copy&paste the "working" code and open new security holes.

Good that it is practice you are writing this little thingy for and if you ask me, even better that you just learned a important lesson. NEVER write unsafe code just 'cause it seems to be less work. Less work = code that mustn't be maintained.

I didn't want to step on any toes or feets, I just can't say it loud enough: don't give out toxics as long one thinks it is a cookie.