[Phpwm] mysql in() lists

[Phil Beynon]

> > Hi all,
> >
> > Is there a more efficient way of making in() list sql other than
> > concatenating them together?
> >
> > Currently I'm doing this;
> >
>
> $sql = "select user_name from imageasset where user_name like
> '%DUMMY_IMAGE.jpg'";
> $cleanup = mysql_query($sql);
> if(!$cleanup){
> 	echo("<p>Error in select cleanup dummy image E66 : " .
> mysql_error() . "</p>");
> 	exit();
> }
> if(mysql_num_rows($cleanup) >= 1){
> 	while($row = mysql_fetch_array($cleanup,MYSQL_ASSOC)){
> 		$in_name = $in_name .  $row["user_name"] . "','";
> 	}
> 	$in_name = substr_replace($in_name,"",(strlen($in_name)-3));
> }
>
> (Sorry for reindenting it, but I found it hard to read)
>
> >
> > which then results in a nice little string along the lines of;
> >
> > username1.jpg','username2.jpg','username3.jpg','....usernamex.jpg','
> >
> > The substr_replace just trims the last ',' off the end.
> > The % in the sql is because there is a random precursor
> sequence added to
> > the filenames.
> >
> > This then gets used as;
> >
> > $sql = "select user_name from Type_B_Image_Tracking where user_name like
> > '%DUMMY_IMAGE.jpg' and user_name not in('$in_name')";
>
> Why can't you do :
>
> SELECT user_name FROM Type_B_Image_Tracking where
> 	user_name like '%DUMMY_IMAGE.jpg' and
> 	user_name not in (
> 		select user_name from imageasset where user_name
> like '%DUMMY_IMAGE.jpg')

That didn't work - I'm using MySQL Release 4.0.24-0 which doesn't support
select in select syntax. This is why it was in two selects to start with :-)


>
>
> ??
>
> You could remove the "," from the end of the string in a better manner
> too, perhaps something like :
>
>
> $ending = "";
> while($something) {
> 	$foo = $ending . " blah blah blah";
> 	$ending = ",";
> }
>
> Technically your code is vulnerable to sql injection where you
> concatenate stuff together (i.e. the file names in the db could contain
> "'").

Shouldn't be, its actually all image filenames and they are passed through a
regex during upload that would strip any nasties or illegal chars / spaces
etc. In actual fact this bit is designed purely to strip out from the
filesystem and database tables anything called XXXX_DUMMY_IMAGE.jpg where X
is a random precode sequence that is added to uploaded images.
This is done because some cameras such as certain Sony ones start from
image000 again when a new memory stick is fitted (found that out the hard
way) and this stops any chance of duplicate filenames being uploaded causing
things to be overwritten

> (I've obviously not tried the SQL, and have little understanding of what
> you're trying to do.... so I could be talking/writing rubbish)

You wouldn't have wanted the whole page posted, it runs to about 70K of code
:-)

Regards,

Phil

>
> David.
> --
> David Goodwin
>
> [ david at codepoets dot co dot uk ]
> [ http://www.codepoets.co.uk       ]
>
> _______________________________________________
> Phpwm mailing list
> Phpwm at mailman.lug.org.uk
> https://mailman.lug.org.uk/mailman/listinfo/phpwm
> Wiki: http://wiki.phpwm.org
>