return multiple rows from sql statement
giloosh
results.
i have a checkbox on each row named checkbox[x]
(x = the current row id)
i will submit the form and take all the rows that are checked and
filter out the ones that are not checked.
so if i had 10 rows and i checked 3 of them then when i submit the
form only those 3 rows will show up.
I was wondering what was the best way to call those rows in the sql
statement.
Would i just loop through the rows that were checked and add them to
the WHERE STATEMENT using the OR condition?
select * from rows where id = 3 or id = 2 or id = 6 or id = 10
so basically i would do something like this with the php code.
$sql = "select * from rows WHERE 1 ";
foreach ($_POST[checkbox] as $check){
$sql .= " OR id = $check";
}
I just think its slow to use so many OR conditions in one sql
statement, but i cant think of anyway else to do this.
Is the way i want to do this a good way?
Thanks for your help
Jan Thomä
> Would i just loop through the rows that were checked and add them to
> the WHERE STATEMENT using the OR condition?
>
> select * from rows where id = 3 or id = 2 or id = 6 or id = 10
>
You could try the IN variant like:
select * from rows where id in ( ?, ?, ?, ?);
This might be a bit faster than the ORs...
Best regards,
Jan
--
_________________________________________________________________________
insOMnia - We never sleep...
http://www.insOMnia-hq.de
giloosh
> giloosh wrote:
> > Would i just loop through the rows that were checked and add them to
I will do a few SQL tests to see which method is faster.
Good Man
@o80g2000hse.googlegroups.com:
> Would i just loop through the rows that were checked and add them to
> the WHERE STATEMENT using the OR condition?
>
> select * from rows where id = 3 or id = 2 or id = 6 or id = 10
>
> so basically i would do something like this with the php code.
> $sql = "select * from rows WHERE 1 ";
> foreach ($_POST[checkbox] as $check){
> $sql .= " OR id = $check";
> }
>
i'll assume this code is here for brevity, and that you're really making
sure that your $check variable contains what you're expecting it to (a
number).
I tend to craft my SQL 'where' statements AFTER checking for variables,
assembling them as a string, and appending them to a query.
ie:
$whereVar = "1";
foreach($_POST['checkbox'] as $check) {
if(is_numeric($check)) {
$whereVar .= " OR id='$check'";
}
}
$sql = "SELECT * FROM rows WHERE $whereVar";
ps: naming a MySQL table "rows" can certainly lead to confusion down the
road... try to stay away from reserved words and their permutations
entirely
> I just think its slow to use so many OR conditions in one sql
> statement, but i cant think of anyway else to do this.
As long as "id" is properly indexed, it won't be slow. You're searching
for results based on conditions... there's no way to avoid searching!
Jerry Stuckle
You could even ask in a SQL group, since this isn't a PHP question.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================
Jan Thomä
>
> i'll assume this code is here for brevity, and that you're really making
> sure that your $check variable contains what you're expecting it to (a
> number).
>
> I tend to craft my SQL 'where' statements AFTER checking for variables,
> assembling them as a string, and appending them to a query.
I always use the placeholder notation for doing SQL. Concatening SQL strings
from input values is almost certainly a safe path to SQL injection. So what
i'd do is:
foreach( ... ) {
$where .= "OR id = ?";
}
and then use a framework like AdoDB to have them replace the placeholders.
Saves a lot of time and problems...
Jerry Stuckle
> Good Man wrote:
>> i'll assume this code is here for brevity, and that you're really making
>> sure that your $check variable contains what you're expecting it to (a
>> number).
>>
>> I tend to craft my SQL 'where' statements AFTER checking for variables,
>> assembling them as a string, and appending them to a query.
>
> I always use the placeholder notation for doing SQL. Concatening SQL strings
> from input values is almost certainly a safe path to SQL injection. So what
> i'd do is:
>
> foreach( ... ) {
> $where .= "OR id = ?";
> }
>
> and then use a framework like AdoDB to have them replace the placeholders.
> Saves a lot of time and problems...
>
> Best regards,
> Jan
>
>
Not if you properly cleanse your input. Ensure numeric values are
really numeric, and string values are processed through
mysql_real_escape_string(), for instance.
Jan Thomä
> Jan Thomä wrote:
>> I always use the placeholder notation for doing SQL. Concatening SQL
>> strings from input values is almost certainly a safe path to SQL
>> injection.
>
> really numeric, and string values are processed through
> mysql_real_escape_string(), for instance.
Thanks for the input, and yes I agree, you should definitely cleanse your
input before feeding it to the database. My point was simply, that when you
give this kind of work to a framework and always use the ? notation, you
are safe from injection, even if you forget to check a single input
variable (which surely happens from time to time). Also you don't have to
do the conversions to different formats manually, so you save a bit of time
and effort.
giloosh
thanks for the feedback...
isn't is safe to just put quotes around the variable
$id = $_POST['id']
$q = "select * from table1 where id = '$id'";
even if id holds none numeric characters, it's still safe... no?
ELINTPimp
No. That introduces sql injection and cross site scripting
vulnerabilities. mysql_real_escape_strings, as previously suggested,
will help prevent against this.
The Natural Philosopher
Dunno what that means, but since Ive been working all day on this sort
of stuff..
1/. $q = "select * from table1 where id = '".$id."'";
is what you want. Note the order of quotes and dots.
2/. On my server - virgin php5 apache2 setup, all POST data gets escaped
with backslashes into a format that is completely digestible by MySql.
So no special processing is needed before inserting or updating data
from $_POST[] derived variables.
3/. One coding bug showed up interestingly when I had done
$new_id=mysql_insert_id;
instead of
$new_id=mysql_insert_id();
The insert of what surprisinlgly was NOT a syntax error, but a string
called "mysql_insert_id" into an integer field resulted in the value
zero being put in. (Javascript gives you a NaN response when you try and
do maths on a string like (1,000 * 1,000) = Not a Number ;-)).
4/. I did have an issue with redisplaying data that had come from a POST
derived form..now normally I update the database, then read the data
from the database back into the form: In this case I was testing 'failed
to update, re-enter some data' and the backslashed stuff gave me issues
with quotes and backslashes.
Basically a variable that might be "Fred Bloggs" became \"Fred Bloggs\"
after being POSTED. This went into mysql fine using the sort of query I
outlined above. And checking using direct database access showed the
data in *there* was in fact "Fred Bloggs"
However if I wanted to reinsert that into e.g. text box as i.e.
value="\"Fred Bloggs\"" it didn't work too well at all. Repeated updates
left me with truncated data and/or multiple backlashes
The key is as follows - in my particular apache php configs anyway - to
do the following.
Any POST data that needs to be inserted into input fields and the like -
goes through this:-
function sanitise($string)
{
$string=stripslashes($string); // remove any backslashes
$string=htmlspecialchars($string); // turn oddities that HTML barfs
// on into ampersand stuff
return $string;
}
If it's data derived from MySQL it comes out 'as written' with
everything correct BUT that can confuse the browser..broswers don't like
<> chars or '", but just running it through htmlspecialchars() is enough
to sort THAT all out. Inverted commas and single quotes get the &blah;
type treatment, and they display fine, and gets the correct stuff into
the POST data when submitting the form - I know cos I sent three hours
testing it.
i.e. when I did a view source my input text box had
value="&wahtever;Fred Bloggs&whatever;"
inside it, but that showed up as "Fred Bloggs" IN the box, and POSTed up
correctly as \"Fred Bloggs\" and went into SQL as "Fred Bloggs"
So the correct php to generate THAT fragment would be
echo ("value=\"".htmlspecialcars($my_value)."\"");
And bugger., Ive just noted that the hardlinked copies of my library -
one and the same file - have been busted into two different ones by my
editor. It must be one of those that does a 'create tmp file, write it,
unlink original, rename temp to original' that I so much hate... The
quickest way to break SUNOS4 linked config files was to use EMACS to
edit them..
man diff..;-)
}
Jerry Stuckle
mysql_real_escape_string is much safer - it takes into consideration the
charset currently in use, for one thing. Also, being a MySQL function,
it knows what MySQL needs or uses.
> 3/. One coding bug showed up interestingly when I had done
>
> $new_id=mysql_insert_id;
>
> instead of
>
> $new_id=mysql_insert_id();
>
> The insert of what surprisinlgly was NOT a syntax error, but a string
> called "mysql_insert_id" into an integer field resulted in the value
> zero being put in. (Javascript gives you a NaN response when you try and
> do maths on a string like (1,000 * 1,000) = Not a Number ;-)).
>
You would have gotten an E_NOTICE if you had them turned on (which you
should have in development).
> 4/. I did have an issue with redisplaying data that had come from a POST
> derived form..now normally I update the database, then read the data
> from the database back into the form: In this case I was testing 'failed
> to update, re-enter some data' and the backslashed stuff gave me issues
> with quotes and backslashes.
>
> Basically a variable that might be "Fred Bloggs" became \"Fred Bloggs\"
> after being POSTED. This went into mysql fine using the sort of query I
> outlined above. And checking using direct database access showed the
> data in *there* was in fact "Fred Bloggs"
>
> However if I wanted to reinsert that into e.g. text box as i.e.
> value="\"Fred Bloggs\"" it didn't work too well at all. Repeated updates
> left me with truncated data and/or multiple backlashes
>
mysql_real_escape_string() solves that problem, also.
> The key is as follows - in my particular apache php configs anyway - to
> do the following.
>
> Any POST data that needs to be inserted into input fields and the like -
> goes through this:-
>
> function sanitise($string)
> {
> $string=stripslashes($string); // remove any backslashes
> $string=htmlspecialchars($string); // turn oddities that HTML barfs
> // on into ampersand stuff
> return $string;
> }
You shouldn't be doing this on data going into the database.
htmlspecialchars() is a display function. Use it after reading out of
the database.
What happens, if for instance, you want to create a report to be
printed, say using PHP's CLI? Or even MySQL's CLI.
> If it's data derived from MySQL it comes out 'as written' with
> everything correct BUT that can confuse the browser..broswers don't like
> <> chars or '", but just running it through htmlspecialchars() is enough
> to sort THAT all out. Inverted commas and single quotes get the &blah;
> type treatment, and they display fine, and gets the correct stuff into
> the POST data when submitting the form - I know cos I sent three hours
> testing it.
>
> i.e. when I did a view source my input text box had
>
> value="&wahtever;Fred Bloggs&whatever;"
>
> inside it, but that showed up as "Fred Bloggs" IN the box, and POSTed up
> correctly as \"Fred Bloggs\" and went into SQL as "Fred Bloggs"
>
> So the correct php to generate THAT fragment would be
>
> echo ("value=\"".htmlspecialcars($my_value)."\"");
>
> And bugger., Ive just noted that the hardlinked copies of my library -
> one and the same file - have been busted into two different ones by my
> editor. It must be one of those that does a 'create tmp file, write it,
> unlink original, rename temp to original' that I so much hate... The
> quickest way to break SUNOS4 linked config files was to use EMACS to
> edit them..
>
> man diff..;-)
>
>
>
>
> }
>
>
The Natural Philosopher
make their way into the database unaltered. That's how I like it.
Ter is no nede to change anything in THAT area.
>> 3/. One coding bug showed up interestingly when I had done
>>
>> $new_id=mysql_insert_id;
>>
>> instead of
>>
>> $new_id=mysql_insert_id();
>>
>> The insert of what surprisinlgly was NOT a syntax error, but a string
>> called "mysql_insert_id" into an integer field resulted in the value
>> zero being put in. (Javascript gives you a NaN response when you try
>> and do maths on a string like (1,000 * 1,000) = Not a Number ;-)).
>>
>
> You would have gotten an E_NOTICE if you had them turned on (which you
> should have in development).
You should not teach your grandmother to suck eggs.
I had my OWN debugging on. I looked at the echoed query strings and
realised that what was going in was weird and didn't match what was in
the database subseqeuntly, Simple innit?
Now thing about all the bugs that E_NOTICE would NOT catch, like having
the data fields transposed so that the guys name goes in his street
address and so on.
Yu would have looked an seen no bugs, I would have looked three imes and
double checked it and fund it. Because I wasn't relying on anything more
than hard concentration and nitpicking over the details.
Why do I get the feeling that you are someone who enjoys displaying a
superfcial knowledge about programming, but doesn't actually do very
much OF it in real world situations?
>
>> 4/. I did have an issue with redisplaying data that had come from a
>> POST derived form..now normally I update the database, then read the
>> data from the database back into the form: In this case I was testing
>> 'failed to update, re-enter some data' and the backslashed stuff gave
>> me issues with quotes and backslashes.
>>
>> Basically a variable that might be "Fred Bloggs" became \"Fred
>> Bloggs\" after being POSTED. This went into mysql fine using the sort
>> of query I outlined above. And checking using direct database access
>> showed the data in *there* was in fact "Fred Bloggs"
>>
>> However if I wanted to reinsert that into e.g. text box as i.e.
>> value="\"Fred Bloggs\"" it didn't work too well at all. Repeated
>> updates left me with truncated data and/or multiple backlashes
>>
>
> mysql_real_escape_string() solves that problem, also.
>
>> The key is as follows - in my particular apache php configs anyway -
>> to do the following.
>>
>> Any POST data that needs to be inserted into input fields and the like -
>> goes through this:-
>>
>> function sanitise($string)
>> {
>> $string=stripslashes($string); // remove any backslashes
>> $string=htmlspecialchars($string); // turn oddities that HTML barfs
>> // on into ampersand stuff
>> return $string;
>> }
>
> You shouldn't be doing this on data going into the database.
If you read what I *said*, I specifically said that I *wasn't*. I was
doing it on data coming from POSTed variables before applying to the
BROWSER.
What part of
"all POST data gets
escaped with backslashes into a format that is completely digestible
by MySql. So no special processing is needed before inserting or
updating data from $_POST[] derived variables."
Do you NOT understand?
You are heading for a plonk.
> htmlspecialchars() is a display function. Use it after reading out of
> the database.
>
That is precisely what I said, dickhead.
> What happens, if for instance, you want to create a report to be
> printed, say using PHP's CLI? Or even MySQL's CLI.
>
I'll write one. However, in this instance I don't.
God, a whole post that merely confirms you didn't even read mine before
jumping in to show off how fucking smart you are (not!).
Jerry Stuckle
Safer than using addslashes().
> Ter is no nede to change anything in THAT area.
>
No, not when you're ignorant about the realities, there isn't.
>
>>> 3/. One coding bug showed up interestingly when I had done
>>>
>>> $new_id=mysql_insert_id;
>>>
>>> instead of
>>>
>>> $new_id=mysql_insert_id();
>>>
>>> The insert of what surprisinlgly was NOT a syntax error, but a string
>>> called "mysql_insert_id" into an integer field resulted in the value
>>> zero being put in. (Javascript gives you a NaN response when you try
>>> and do maths on a string like (1,000 * 1,000) = Not a Number ;-)).
>>>
>>
>> You would have gotten an E_NOTICE if you had them turned on (which you
>> should have in development).
>
> You should not teach your grandmother to suck eggs.
>
You shouldn't complain about not getting messages when you have those
very messages disabled.
> I had my OWN debugging on. I looked at the echoed query strings and
> realised that what was going in was weird and didn't match what was in
> the database subseqeuntly, Simple innit?
>
Yes, and if you would have had the E_NOTICE turned on, you would have
gotten the message you bitched about not getting. You have no one to
blame but yourself.
> Now thing about all the bugs that E_NOTICE would NOT catch, like having
> the data fields transposed so that the guys name goes in his street
> address and so on.
>
That's not a bug. That's a programmer deficiency.
> Yu would have looked an seen no bugs, I would have looked three imes and
> double checked it and fund it. Because I wasn't relying on anything more
> than hard concentration and nitpicking over the details.
>
And if you would have had E_NOTICE enabled, you would have known the
exact line number of the error the first time you executed the code.
> Why do I get the feeling that you are someone who enjoys displaying a
> superfcial knowledge about programming, but doesn't actually do very
> much OF it in real world situations?
>
>
Been programming for 40 years, asshole. Almost 20 different languages
over that time. And been a successful consultant for the last 17 years.
I probably write more code in a week than you do in a year. At least
*good code*.
Learn from your elders, asshole.
No, you weren't at all clear about that.
> What part of
>
> "all POST data gets
> escaped with backslashes into a format that is completely digestible
> by MySql. So no special processing is needed before inserting or
> updating data from $_POST[] derived variables."
>
> Do you NOT understand?
>
I understand when someone is clear with their words.
> You are heading for a plonk.
Thank you. From assholes like you, it's an honor.
>
>> htmlspecialchars() is a display function. Use it after reading out of
>> the database.
>>
>
> That is precisely what I said, dickhead.
>
Then be clear about what you write, asshole.
>> What happens, if for instance, you want to create a report to be
>> printed, say using PHP's CLI? Or even MySQL's CLI.
>>
>
> I'll write one. However, in this instance I don't.
>
But the key is you don't know if you'll need one tomorrow, or six months
from now, or never. You've obviously never been on a project which
lasted more then four hours or you'd know that.
> God, a whole post that merely confirms you didn't even read mine before
> jumping in to show off how fucking smart you are (not!).
>
Bullshit. It just goes to show how unclear you were with your posting,
asshole.
>
>>> If it's data derived from MySQL it comes out 'as written' with
>>> everything correct BUT that can confuse the browser..broswers don't
>>> like <> chars or '", but just running it through htmlspecialchars()
>>> is enough to sort THAT all out. Inverted commas and single quotes get
>>> the &blah; type treatment, and they display fine, and gets the
>>> correct stuff into the POST data when submitting the form - I know
>>> cos I sent three hours testing it.
>>>
>>> i.e. when I did a view source my input text box had
>>>
>>> value="&wahtever;Fred Bloggs&whatever;"
>>>
>>> inside it, but that showed up as "Fred Bloggs" IN the box, and POSTed
>>> up correctly as \"Fred Bloggs\" and went into SQL as "Fred Bloggs"
>>>
>>> So the correct php to generate THAT fragment would be
>>>
>>> echo ("value=\"".htmlspecialcars($my_value)."\"");
>>>
>>> And bugger., Ive just noted that the hardlinked copies of my library
>>> - one and the same file - have been busted into two different ones by
>>> my editor. It must be one of those that does a 'create tmp file,
>>> write it, unlink original, rename temp to original' that I so much
>>> hate... The quickest way to break SUNOS4 linked config files was to
>>> use EMACS to edit them..
>>>
>>> man diff..;-)
>>>
>>>
>>>
>>>
>>> }
>>>
>>>
>>
>>
This is really funny. You're totally ignorant, but trying to give
advice. I'd recommend you stick your head back up your ass where it
normally is, and learn to program before giving advice.
Because your advice is shit.
Rik Wasmus
>>>>> isn't is safe to just put quotes around the variable
>>>>> $id = $_POST['id']
>>>>>
>>>>> $q = "select * from table1 where id = '$id'";
>>>>>
>>>>> even if id holds none numeric characters, it's still safe... no?
>>>>
>>>> No. That introduces sql injection and cross site scripting
>>>> vulnerabilities. mysql_real_escape_strings, as previously suggested,
>>>> will help prevent against this.
>>>>
>>>
>>> Dunno what that means, but since Ive been working all day on this sort
>>> of stuff..
>>>
>>> 1/. $q = "select * from table1 where id = '".$id."'";
>>>
>>> is what you want. Note the order of quotes and dots.
>>>
>>> 2/. On my server - virgin php5 apache2 setup, all POST data gets
>>> escaped with backslashes into a format that is completely digestible
>>> by MySql. So no special processing is needed before inserting or
>>> updating data from $_POST[] derived variables.
>>>
>> mysql_real_escape_string is much safer - it takes into consideration
>> the charset currently in use, for one thing. Also, being a MySQL
>> function, it knows what MySQL needs or uses.
>>
> Sager than what? Nothing? All characters that are entered in the fields
> make their way into the database unaltered. That's how I like it.
http://nl3.php.net/manual/en/ref.info.php#ini.magic-quotes-gpc
"all ' (single-quote), " (double quote), \ (backslash) and NUL's are
escaped with a backslash"
Which isn't all that needs to be escaped. For an example:
http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string
Also, mysql_real_escape_string() will take care of NOT altering data when
inserting it, providing you don;t have any hidious mechanisms like magic
quotes enabled.
> Ter is no nede to change anything in THAT area.
There really is.
>>> 3/. One coding bug showed up interestingly when I had done
>>>
>>> $new_id=mysql_insert_id;
>>>
>>> instead of
>>>
>>> $new_id=mysql_insert_id();
>>>
>>> The insert of what surprisinlgly was NOT a syntax error, but a string
>>> called "mysql_insert_id" into an integer field resulted in the value
>>> zero being put in. (Javascript gives you a NaN response when you try
>>> and do maths on a string like (1,000 * 1,000) = Not a Number ;-)).
>>>
>> You would have gotten an E_NOTICE if you had them turned on (which you
>> should have in development).
>
> You should not teach your grandmother to suck eggs.
>
> I had my OWN debugging on.
Your OWN? You know better how to parse PHP then the people who make it?
> I looked at the echoed query strings and realised that what was going in
> was weird and didn't match what was in the database subseqeuntly, Simple
> innit?
>
> Now thing about all the bugs that E_NOTICE would NOT catch, like having
> the data fields transposed so that the guys name goes in his street
> address and so on.
Indeed, next to normal PHP error reporting you should
log/echo/write/report (to your liking) application errors. Which are quite
different from script errors.
> Yu would have looked an seen no bugs, I would have looked three imes and
> double checked it and fund it. Because I wasn't relying on anything more
> than hard concentration and nitpicking over the details.
And spending way to much time... Either exceptions or trigger_error()s
would provide you with apt debugging info for your inner workings with the
same mechanism PHP uses. Why split them?
> Why do I get the feeling that you are someone who enjoys displaying a
> superfcial knowledge about programming, but doesn't actually do very
> much OF it in real world situations?
Dunno. Because you're paranoid?
>>> 4/. I did have an issue with redisplaying data that had come from a
>>> POST derived form..now normally I update the database, then read the
>>> data from the database back into the form: In this case I was testing
>>> 'failed to update, re-enter some data' and the backslashed stuff gave
>>> me issues with quotes and backslashes.
>>>
>>> Basically a variable that might be "Fred Bloggs" became \"Fred
>>> Bloggs\" after being POSTED.
It should not automagically happen. It should be a 'conscious descision'
of the code on insertion into a database.
>>> This went into mysql fine using the sort of query I outlined above.
>>> And checking using direct database access showed the data in *there*
>>> was in fact "Fred Bloggs"
>>>
>>> However if I wanted to reinsert that into e.g. text box as i.e.
>>> value="\"Fred Bloggs\"" it didn't work too well at all. Repeated
>>> updates left me with truncated data and/or multiple backlashes
>>>
>> mysql_real_escape_string() solves that problem, also.
>>
>>> The key is as follows - in my particular apache php configs anyway -
>>> to do the following.
>>>
>>> Any POST data that needs to be inserted into input fields and the like
>>> -
>>> goes through this:-
>>>
>>> function sanitise($string)
>>> {
>>> $string=stripslashes($string); // remove any backslashes
>>> $string=htmlspecialchars($string); // turn oddities that HTML barfs
>>> // on into ampersand stuff
>>> return $string;
>>> }
Odd combination. And as a general rule: stripslashes is seldomly a good
thing in code. Normally it indicates someone or some code fucked things up
earlier on.
>> You shouldn't be doing this on data going into the database.
>
> If you read what I *said*, I specifically said that I *wasn't*. I was
> doing it on data coming from POSTed variables before applying to the
> BROWSER.
>
> What part of
>
> "all POST data gets
> escaped with backslashes into a format that is completely digestible
> by MySql. So no special processing is needed before inserting or
> updating data from $_POST[] derived variables."
>
> Do you NOT understand?
>
> You are heading for a plonk.
You are heading for a major embarrassment as soon as you learn/realize how
things really work.
>> htmlspecialchars() is a display function. Use it after reading out of
>> the database.
>>
>
> That is precisely what I said, dickhead.
Damn, I was gonna post further. There's no reason for this kind of
language though. Let's sum up by saying you know some things, but the
details are clearly lost on you. You should refrain from giving people
advise or at least include a disclaimer you're just an amateur.
--
Rik Wasmus
My new ISP's newsserver sucks. Anyone recommend a good one? Paying for
quality is certainly an option.
Rik Wasmus
<jstu...@attglobal.net> wrote:
> giloosh wrote:
>> On Aug 29, 12:20 pm, Jan Thomä <k...@insomnia-hq.de> wrote:
>>> giloosh wrote:
>>>> Would i just loop through the rows that were checked and add them to
>> Thanks Jan.
>> I will do a few SQL tests to see which method is faster.
>>>> the WHERE STATEMENT using the OR condition?
>>>> select * from rows where id = 3 or id = 2 or id = 6 or id = 10
>>> You could try the IN variant like:
>>>
>>> select * from rows where id in ( ?, ?, ?, ?);
>>>
>>> This might be a bit faster than the ORs...
>>>
>>> Best regards,
>>> Jan
>
> You could even ask in a SQL group, since this isn't a PHP question.
Allthough the advice make sense :)
Michael Fesser
>Dunno what that means, but since Ive been working all day on this sort
>of stuff..
>
>1/. $q = "select * from table1 where id = '".$id."'";
>
>is what you want. Note the order of quotes and dots.
Only if the ID is a string. Numeric values are not quoted. And with
embedded variables or sprintf() you won't even have to worry about the
order of quotes and dots, which is a really error-prone style of writing
a query:
$q = "SELECT * FROM table1 WHERE id = $id";
$q = sprintf("SELECT * FROM table1 WHERE id = %u", $id);
>2/. On my server - virgin php5 apache2 setup, all POST data gets escaped
>with backslashes into a format that is completely digestible by MySql.
Relying on magic quotes is unreliable and won't work at all in PHP 6.
Better start fixing your code now.
>3/. One coding bug showed up interestingly when I had done
>
>$new_id=mysql_insert_id;
>
>instead of
>
>$new_id=mysql_insert_id();
>
>The insert of what surprisinlgly was NOT a syntax error
Your error_reporting is set improperly.
>4/. I did have an issue with redisplaying data that had come from a POST
>derived form..now normally I update the database, then read the data
>from the database back into the form: In this case I was testing 'failed
>to update, re-enter some data' and the backslashed stuff gave me issues
>with quotes and backslashes.
A typical magic quotes problem.
>Basically a variable that might be "Fred Bloggs" became \"Fred Bloggs\"
>after being POSTED. This went into mysql fine using the sort of query I
>outlined above. And checking using direct database access showed the
>data in *there* was in fact "Fred Bloggs"
>
>However if I wanted to reinsert that into e.g. text box as i.e.
>value="\"Fred Bloggs\"" it didn't work too well at all. Repeated updates
>left me with truncated data and/or multiple backlashes
A bug in your code. Usually all you have to do is this:
* when receiving the POST data, remove slashes if magic quotes are on
* use a proper escaping function to insert the data into the DB
* when printing it out, use htmlspecialchars()
That's it. Correct, reliable and no problem with slashes.
>Any POST data that needs to be inserted into input fields and the like -
>goes through this:-
>
>function sanitise($string)
>{
>$string=stripslashes($string); // remove any backslashes
>$string=htmlspecialchars($string); // turn oddities that HTML barfs
> // on into ampersand stuff
>return $string;
>}
If you have to call stripslashes() on output you've made a mistake
somewhere else. It's never necessary for printing out something.
>inside it, but that showed up as "Fred Bloggs" IN the box, and POSTed up
>correctly as \"Fred Bloggs\" and went into SQL as "Fred Bloggs"
>
>So the correct php to generate THAT fragment would be
>
>echo ("value=\"".htmlspecialcars($my_value)."\"");
printf("value='%s'", htmlspecialcars($my_value));
Micha
The Natural Philosopher
I never mentioned addslashes
>
>> Ter is no nede to change anything in THAT area.
>>
>
> No, not when you're ignorant about the realities, there isn't.
>
No when yu refuse to read anthing that is writtn.
>>
>>>> 3/. One coding bug showed up interestingly when I had done
>>>>
>>>> $new_id=mysql_insert_id;
>>>>
>>>> instead of
>>>>
>>>> $new_id=mysql_insert_id();
>>>>
>>>> The insert of what surprisinlgly was NOT a syntax error, but a
>>>> string called "mysql_insert_id" into an integer field resulted in
>>>> the value zero being put in. (Javascript gives you a NaN response
>>>> when you try and do maths on a string like (1,000 * 1,000) = Not a
>>>> Number ;-)).
>>>>
>>>
>>> You would have gotten an E_NOTICE if you had them turned on (which
>>> you should have in development).
>>
>> You should not teach your grandmother to suck eggs.
>>
>
> You shouldn't complain about not getting messages when you have those
> very messages disabled.
I didn't complain about no getting messages. You did.
>
>> I had my OWN debugging on. I looked at the echoed query strings and
>> realised that what was going in was weird and didn't match what was in
>> the database subseqeuntly, Simple innit?
>>
>
> Yes, and if you would have had the E_NOTICE turned on, you would have
> gotten the message you bitched about not getting. You have no one to
> blame but yourself.
>
I didn't bitch about anything. I made an error, I tested and checked and
found the error, like I always do.
The point of mentioning it was that it is what happens when yiu try and
insert non numerical data nto a numerical feld. SQL is smart and des its
best.
>> Now thing about all the bugs that E_NOTICE would NOT catch, like
>> having the data fields transposed so that the guys name goes in his
>> street address and so on.
>>
>
> That's not a bug. That's a programmer deficiency.
>
They are all programmer errors. My god it gets more obvious that you
haven't written software profesoionally ever.
>> Yu would have looked an seen no bugs, I would have looked three imes
>> and double checked it and fund it. Because I wasn't relying on
>> anything more than hard concentration and nitpicking over the details.
>>
>
> And if you would have had E_NOTICE enabled, you would have known the
> exact line number of the error the first time you executed the code.
>
>> Why do I get the feeling that you are someone who enjoys displaying a
>> superfcial knowledge about programming, but doesn't actually do very
>> much OF it in real world situations?
>>
>>
>
> Been programming for 40 years, asshole. Almost 20 different languages
> over that time. And been a successful consultant for the last 17 years.
>
Bollocks.No one would employ you with that attitude.
> I probably write more code in a week than you do in a year. At least
> *good code*.
>
> Learn from your elders, asshole.
>
I am older than you then..
No, you are simply too arrogant to read things properly.
>> What part of
>>
>> "all POST data gets
>> escaped with backslashes into a format that is completely digestible
>> by MySql. So no special processing is needed before inserting or
>> updating data from $_POST[] derived variables."
>>
>> Do you NOT understand?
>>
>
> I understand when someone is clear with their words.
>
No you do not.
>> You are heading for a plonk.
>
> Thank you. From assholes like you, it's an honor.
>
>>
>>> htmlspecialchars() is a display function. Use it after reading out
>>> of the database.
>>>
>>
>> That is precisely what I said, dickhead.
>>
>
> Then be clear about what you write, asshole.
>
>>> What happens, if for instance, you want to create a report to be
>>> printed, say using PHP's CLI? Or even MySQL's CLI.
>>>
>>
>> I'll write one. However, in this instance I don't.
>>
>
> But the key is you don't know if you'll need one tomorrow, or six months
> from now, or never. You've obviously never been on a project which
> lasted more then four hours or you'd know that.
>
Actually I DO know exactly what I will need, since I am the one
specifying this, and I know exactly what it needs to do.
plonk,
The Natural Philosopher
> .oO(The Natural Philosopher)
>
>> Dunno what that means, but since Ive been working all day on this sort
>> of stuff..
>>
>> 1/. $q = "select * from table1 where id = '".$id."'";
>>
>> is what you want. Note the order of quotes and dots.
>
> Only if the ID is a string. Numeric values are not quoted. And with
> embedded variables or sprintf() you won't even have to worry about the
> order of quotes and dots, which is a really error-prone style of writing
> a query:
>
> $q = "SELECT * FROM table1 WHERE id = $id";
>
> $q = sprintf("SELECT * FROM table1 WHERE id = %u", $id);
>
That is worth doing: The overhead on printf/sprintf is massive compared
with a print, and especially an echo statement. I tend to avoid it
whenever possible but a databse update is a slow thing anyway, so its
probably worth using it.
>> 2/. On my server - virgin php5 apache2 setup, all POST data gets escaped
>> with backslashes into a format that is completely digestible by MySql.
>
> Relying on magic quotes is unreliable and won't work at all in PHP 6.
> Better start fixing your code now.
>
>> 3/. One coding bug showed up interestingly when I had done
>>
>> $new_id=mysql_insert_id;
>>
>> instead of
>>
>> $new_id=mysql_insert_id();
>>
>> The insert of what surprisinlgly was NOT a syntax error
>
> Your error_reporting is set improperly.
>
*shrug* improperly as defined by who?
The world runs on opinion, stated as fact....
>> 4/. I did have an issue with redisplaying data that had come from a POST
>> derived form..now normally I update the database, then read the data
>>from the database back into the form: In this case I was testing 'failed
>> to update, re-enter some data' and the backslashed stuff gave me issues
>> with quotes and backslashes.
>
> A typical magic quotes problem.
>
>> Basically a variable that might be "Fred Bloggs" became \"Fred Bloggs\"
>> after being POSTED. This went into mysql fine using the sort of query I
>> outlined above. And checking using direct database access showed the
>> data in *there* was in fact "Fred Bloggs"
>>
>> However if I wanted to reinsert that into e.g. text box as i.e.
>> value="\"Fred Bloggs\"" it didn't work too well at all. Repeated updates
>> left me with truncated data and/or multiple backlashes
>
> A bug in your code. Usually all you have to do is this:
>
> * when receiving the POST data, remove slashes if magic quotes are on
> * use a proper escaping function to insert the data into the DB
> * when printing it out, use htmlspecialchars()
>
> That's it. Correct, reliable and no problem with slashes.
>
Well that's another way. Ends up with the same number of manipulations...
>> Any POST data that needs to be inserted into input fields and the like -
>> goes through this:-
>>
>> function sanitise($string)
>> {
>> $string=stripslashes($string); // remove any backslashes
>> $string=htmlspecialchars($string); // turn oddities that HTML barfs
>> // on into ampersand stuff
>> return $string;
>> }
>
> If you have to call stripslashes() on output you've made a mistake
> somewhere else. It's never necessary for printing out something.
>
again, it wasnt for printing: the magic_quotes applied it to post data.
It ws for re0insertin into formss.
>> inside it, but that showed up as "Fred Bloggs" IN the box, and POSTed up
>> correctly as \"Fred Bloggs\" and went into SQL as "Fred Bloggs"
>>
>> So the correct php to generate THAT fragment would be
>>
>> echo ("value=\"".htmlspecialcars($my_value)."\"");
>
> printf("value='%s'", htmlspecialcars($my_value));
>
Printf is slow, and an unnecessary overhead. At some point you have to
decide between speed and coding clarity. PHP itself is such a
compromise: for choice I'd have written it in C, but PHP is a reasoable
if quirky compromise between development speed and run time speed.
> Micha
Michael Fesser
>Michael Fesser wrote:
>
>> Only if the ID is a string. Numeric values are not quoted. And with
>> embedded variables or sprintf() you won't even have to worry about the
>> order of quotes and dots, which is a really error-prone style of writing
>> a query:
>>
>> $q = "SELECT * FROM table1 WHERE id = $id";
>>
>> $q = sprintf("SELECT * FROM table1 WHERE id = %u", $id);
>
>That is worth doing: The overhead on printf/sprintf is massive compared
>with a print, and especially an echo statement.
Of course it is, but it's only an issue if you call it a million times
in a loop. In normal code you won't be able to notice a difference.
(s)printf() has many advantages, especially when you want to build a
string with many embedded variables or even complex expressions. Just
using echo and string concatenation could lead to really ugly code.
>> Your error_reporting is set improperly.
>>
>*shrug* improperly as defined by who?
>The world runs on opinion, stated as fact....
While developing error_reporting should be set to E_ALL | E_STRICT.
Proper and correct code doesn't throw any notices, because even a notice
can be the reason of really nasty bugs.
>> A bug in your code. Usually all you have to do is this:
>>
>> * when receiving the POST data, remove slashes if magic quotes are on
>> * use a proper escaping function to insert the data into the DB
>> * when printing it out, use htmlspecialchars()
>>
>> That's it. Correct, reliable and no problem with slashes.
>
>Well that's another way. Ends up with the same number of manipulations...
Actually it works, while yours obviously doesn't.
>>> Any POST data that needs to be inserted into input fields and the like -
>>> goes through this:-
>>>
>>> function sanitise($string)
>>> {
>>> $string=stripslashes($string); // remove any backslashes
>>> $string=htmlspecialchars($string); // turn oddities that HTML barfs
>>> // on into ampersand stuff
>>> return $string;
>>> }
>>
>> If you have to call stripslashes() on output you've made a mistake
>> somewhere else. It's never necessary for printing out something.
>>
>
>again, it wasnt for printing: the magic_quotes applied it to post data.
>It ws for re0insertin into formss.
Yes, and this means output/printing to an HTML page.
>> printf("value='%s'", htmlspecialcars($my_value));
>>
>Printf is slow, and an unnecessary overhead.
As said, it has its advantages.
>At some point you have to
>decide between speed and coding clarity.
(s)printf() is not an issue here. You might want to read about premature
optimization if you like. Use a profiler to find the real bottlenecks in
your code.
Micha
Norman Peelman
Well, you did say that all POST data was getting escaped with backslashes...
>>
>>> Ter is no nede to change anything in THAT area.
>>>
>>
>> No, not when you're ignorant about the realities, there isn't.
>>
> No when yu refuse to read anthing that is writtn.
>>>
>>>>> 3/. One coding bug showed up interestingly when I had done
>>>>>
>>>>> $new_id=mysql_insert_id;
>>>>>
>>>>> instead of
>>>>>
>>>>> $new_id=mysql_insert_id();
>>>>>
>>>>> The insert of what surprisinlgly was NOT a syntax error, but a
>>>>> string called "mysql_insert_id" into an integer field resulted in
>>>>> the value zero being put in. (Javascript gives you a NaN response
>>>>> when you try and do maths on a string like (1,000 * 1,000) = Not a
>>>>> Number ;-)).
>>>>>
... and you should know that trying to use a string as a number will
result in 0 if there are no numbers in the string. So, not a MySQL error
or a PHP error but a programming error that would have been caught with
the proper error reporting on.
>>>>
>>>> You would have gotten an E_NOTICE if you had them turned on (which
>>>> you should have in development).
>>>
>>> You should not teach your grandmother to suck eggs.
>>>
>>
>> You shouldn't complain about not getting messages when you have those
>> very messages disabled.
> I didn't complain about no getting messages. You did.
>
>>
>>> I had my OWN debugging on. I looked at the echoed query strings and
>>> realised that what was going in was weird and didn't match what was
>>> in the database subseqeuntly, Simple innit?
>>>
MySQL doesn't alter the data it stores... would be very much of a
database if it did.
>>
>> Yes, and if you would have had the E_NOTICE turned on, you would have
>> gotten the message you bitched about not getting. You have no one to
>> blame but yourself.
>>
> I didn't bitch about anything. I made an error, I tested and checked and
> found the error, like I always do.
>
> The point of mentioning it was that it is what happens when yiu try and
> insert non numerical data nto a numerical feld. SQL is smart and des its
> best.
Had nothing to do with MySQL.
>
>>> Now thing about all the bugs that E_NOTICE would NOT catch, like
>>> having the data fields transposed so that the guys name goes in his
>>> street address and so on.
>>>
>>
>> That's not a bug. That's a programmer deficiency.
>>
> They are all programmer errors. My god it gets more obvious that you
> haven't written software profesoionally ever.
>
>>> Yu would have looked an seen no bugs, I would have looked three imes
>>> and double checked it and fund it. Because I wasn't relying on
>>> anything more than hard concentration and nitpicking over the details.
>>>
>>
>> And if you would have had E_NOTICE enabled, you would have known the
>> exact line number of the error the first time you executed the code.
>>
>>> Why do I get the feeling that you are someone who enjoys displaying a
>>> superfcial knowledge about programming, but doesn't actually do very
>>> much OF it in real world situations?
>>>
>>>
>>
>> Been programming for 40 years, asshole. Almost 20 different languages
>> over that time. And been a successful consultant for the last 17 years.
>>
>
> Bollocks.No one would employ you with that attitude.
>
Actually, it's the other way around with your programming practices.
>> I probably write more code in a week than you do in a year. At least
>> *good code*.
>>
>> Learn from your elders, asshole.
>>
>
> I am older than you then..
>
>>>>
>>>>> 4/. I did have an issue with redisplaying data that had come from a
>>>>> POST derived form..now normally I update the database, then read
>>>>> the data from the database back into the form: In this case I was
>>>>> testing 'failed to update, re-enter some data' and the backslashed
>>>>> stuff gave me issues with quotes and backslashes.
>>>>>
>>>>> Basically a variable that might be "Fred Bloggs" became \"Fred
>>>>> Bloggs\" after being POSTED. This went into mysql fine using the
>>>>> sort of query I outlined above. And checking using direct database
>>>>> access showed the data in *there* was in fact "Fred Bloggs"
>>>>>
>>>>> However if I wanted to reinsert that into e.g. text box as i.e.
>>>>> value="\"Fred Bloggs\"" it didn't work too well at all. Repeated
>>>>> updates left me with truncated data and/or multiple backlashes
Not sure why you would need to store double quotes around text in MySQL
but it's your application. This is a problem bewtween the quotes in your
strings and the quotes in your HTML.
>>>>>
>>>>
>>>> mysql_real_escape_string() solves that problem, also.
>>>>
>>>>> The key is as follows - in my particular apache php configs anyway
>>>>> - to do the following.
>>>>>
>>>>> Any POST data that needs to be inserted into input fields and the
>>>>> like -
>>>>> goes through this:-
>>>>>
>>>>> function sanitise($string)
>>>>> {
>>>>> $string=stripslashes($string); // remove any backslashes
>>>>> $string=htmlspecialchars($string); // turn oddities that HTML barfs
>>>>> // on into ampersand stuff
>>>>> return $string;
>>>>> }
>>>>
>>>> You shouldn't be doing this on data going into the database.
>>>
>>> If you read what I *said*, I specifically said that I *wasn't*. I was
>>> doing it on data coming from POSTed variables before applying to the
>>> BROWSER.
>>>
>>
>> No, you weren't at all clear about that.
>>
>
> No, you are simply too arrogant to read things properly.
>
and what part of 'POST data' implies that the data was from MySQL. I can
deduce what you mean but you need to get your terminology right. Data
coming out of a database is not POST data.
>>> What part of
>>>
>>> "all POST data gets
>>> escaped with backslashes into a format that is completely digestible
>>> by MySql. So no special processing is needed before inserting or
>>> updating data from $_POST[] derived variables."
>>>
and what is that format?
The Natural Philosopher
Well it does.
>>>> Any POST data that needs to be inserted into input fields and the like -
>>>> goes through this:-
>>>>
>>>> function sanitise($string)
>>>> {
>>>> $string=stripslashes($string); // remove any backslashes
>>>> $string=htmlspecialchars($string); // turn oddities that HTML barfs
>>>> // on into ampersand stuff
>>>> return $string;
>>>> }
>>> If you have to call stripslashes() on output you've made a mistake
>>> somewhere else. It's never necessary for printing out something.
>>>
>> again, it wasnt for printing: the magic_quotes applied it to post data.
>> It ws for re0insertin into formss.
>
> Yes, and this means output/printing to an HTML page.
>
Semantics.
All I am saying is that if something is coining in from a POST with
backslashes and quotes, to set it up right for web printing if thats
what you call it, it has to be de backslashed and special-charred.
If you take the magic off, it has to BE backslashed for SQL and still
has to be html special charred for the screen.
Thats two explicit coded transformations instead of one implicit (the
'magic') and one explicit.
I am sure we could argue all week about subtleties of style, and detail
where one or the other is better, but I have code to write, and I can't
be arsed.
>>> printf("value='%s'", htmlspecialcars($my_value));
>>>
>> Printf is slow, and an unnecessary overhead.
>
if it had no advantages it would never have been written. But writing a
200 long SELECT <OPTION >statement with a call to printf for each one..
Maybe I am old fashioned, but I try not to use sledgehammers to crack
nuts, especially in loops. Comes from starting out with a Z80 ad 64K ram
and an Assembler...
>> At some point you have to
>> decide between speed and coding clarity.
>
> (s)printf() is not an issue here. You might want to read about premature
> optimization if you like. Use a profiler to find the real bottlenecks in
> your code.
>
well the slows thing is digesting large amounts of javascript ;-)
But thats outside the scope here.
> Micha
Michael Fesser
>All I am saying is that if something is coining in from a POST with
>backslashes and quotes, to set it up right for web printing if thats
>what you call it, it has to be de backslashed and special-charred.
And all I am saying is that in a proper control flow there's no need to
de-backslash anything, because no unnecessary backslashes would be
stored anywhere.
>If you take the magic off, it has to BE backslashed for SQL
True, but the escaping is just for the transport into the DB table, the
backslashes are not stored there.
>and still
>has to be html special charred for the screen.
Correct. But that's the _only_ thing which is necessary for output.
>>> Printf is slow, and an unnecessary overhead.
>>
>if it had no advantages it would never have been written. But writing a
>200 long SELECT <OPTION >statement with a call to printf for each one..
Still no problem: <http://mfesser.de/test/sprintf.php>
Source code: <http://mfesser.de/test/sprintf.php?source>
The differences are absolutely negligible.
>Maybe I am old fashioned, but I try not to use sledgehammers to crack
>nuts, especially in loops.
That's OK, but the real bottlenecks (especially in PHP code) are always
in other places. I prefer code legibility over performance, and for me
an sprintf() call is much more readable than anything else, especially
if there are many variables or expressions involved. The performance hit
is not an issue (if it could be called a hit at all).
Micha