Re: 2 small queries vs 1 big
Roman Ivanov wrote:
> Could you please explain why? Is it because of the
> query overhead?
Depend on what you mean by overhead. If we'd say, overhead is parsing, building execution plan, and allocating memory for the resultset, there's at least one more factor important for performance, the network traffic (Sending the request and receiving the result).
>
> #BTW what is the datatype of u.id and g.id? If its
> a numeric type you
> #shouldn't quote your value.
>
> I'm usually coding in PHP, so I always quote my
> values to prevent possible sql injection attacks.
> Does quoting numbers have some negative effect?
Could have. At the very least, an implicit conversion has to be done.
for data that's supposed to be integer, and i want to use it to generate a query string, i usually validate the data in php:
if (is_numeric($myint)){
$myint = intval($myint);
} else {
// deal with it.
}
or something like that. I think this is quite safe for integer data (Can anyone confirm or deny this? Does anyone have a better trick? Please let me know)
A generic way is to use the mysqli extension, and use parameterized prepared statements:
$mysqli = new mysqli(.....);
$query = '
SELECT u.id, u.groupId, u.name, u.info
FROM user AS u
WHERE u.id = ?
';
$stmt = $mysqli->prepare($query);
$stmt->bind_param('i',$myInt);
etc.
BTW, you should realise that quoting alone is not really a protection. Suppose someone sends a string like:
' or benchmark(999999999999999999,"select power(2,256)") or 1 or '
Without extra precaution, your query would now read:
SELECT u.id, u.groupId, u.name, u.info
FROM user AS u
WHERE u.id = '' or benchmark(999999999999999999,"select power(2,256)") or 1 or ''
So, instead of getting just one or zero rows, we are now fetching all the rows from the user table, and we are giving the server some extra work executing the benchmark
Now, this might not be really malicious, the point is, someone is actually injecting something.
Both the mysql and the mysqli extensions come with a function that helps a bit: mysql_real_escape_string and mysqli->real_escape_string. This escapes the string argument so that it will always remain a valid literal string constant.
So, if you really need to generate the values in the statement, and you want to prevent injection, I really recommed you use these. Quoting alone is not enough.