I don’t know, but after making regular MySQLi query with PHP 7 like:
SELECT *, user.id as id from user
LEFT JOIN test ON test.userid=user.id and test.exw='0' and test.exa='5'
LEFT JOIN sub ON sub.who='$user' AND sub.to=user.id AND sub.xid='0'
WHERE sub.id IS NULL and user.pri<='1' and not user.id='$user' and
not user.status ='Suspended' group by user.id limit 5
It works really slow, Is it possible to make this faster? There is only like 10k rows in table user. I need to wait like 8 seconds for the results, I can remove one of the JOIN like sub and bring it inside, but maybe any general tips for faster queries?
You are prone to SQL Injections. I don’t get why people are so backwards when they use mysqli_*, but are forwards when they use PDO. My guess is, people are used to stuffing raw data into the query like they’re still using mysql_* or they just copy&paste things to see if it all works. Nevertheless, it is dangerous what you are doing. You allow hackers to exploit your logic. Without escaping user input, you will most likely lose user data or even get your website hacked. This is why prepared statementsSHOULD ALWAYS be used when there is a WHERE clause or any user input.
Thank you for your reply, but this is not user input data “$user” is taken from MySQLi database, auto generated id by the system how this is prone to SQL Injections, system hacks the system?. and anyways the variable can be nicely escaped and checked(verified) before delivered to that point of MySQLi insertion without prepared statments, $vars included in MySQL doesn’t mean that all $vars can be just set by the user whatever he wishes, it’s nothing near that, overreacting? You mean that all sites without prepared statement system can be hacked, I don’t agree at all with that. If you know how to code there will be no problems, PHP offers a lot of alternatives, or am I wrong?
And using MySQL indexes fill improve speed a lot, but maybe anything to do with PHP side, about speed
.
What if one day you grab the value from either $_GET or $_POST and forget to make it use a prepared statement? You’d open up a nice security hole. By making any query where you’re plugging in an external variable use a prepared statement, if you then change a query to use a user submitted value you won’t accidentally create a security hole
There’s no doubt mysqli_* has better speed. However, you suggest that data that are retrieved from the database should not have an impact on being SQL Injected. But I believe this is wrong. When outputting something from the database, you SHOULD ALWAYS escape. If you are retrieving data to put in another query, I suggest using prepared statements.
Validate OR sanitize on input and escape on output.
So say you disregard the fact that your data you retrieved could potentially have malicious data. What would you do in a situation where your website is constantly being under attacked and that SQL Injections could possibly be the root of the problem?
Would you then start to consider using prepared statements or are you just going to keep stuffing raw data like you’re still using mysql_*?
If I remove the group by, it’s displays the same row several times not like group by, I can’t remove it (table values aren’t 1to1), limit purpose is to display only 5 results to the user not 10k. and load more if needed. also these results are displayed with “for loop”, anyways I can remove the limit and add it directly to for loop, but I always thought some sort that limiting in MySQLi speeds things up.
Escaping the retrieved db data for another checking in db 2, of course it is needed most of the time but not always, like retrieving an auto generated id number from INT type db row(type that can contain only numbers 1-9), there is no reason to escape it. Should I ALWAYS. in this point there is no logical need, because the data is 100% secured already by the system like my $user vars. I don’t get in that point, can system really throw errors and by retrieving the id it generates some abuse value, some sort of error, in the larger server system, in that case I can understand you, but is this really a possibility?
Are you sure? Do you print the IDs on the webpage somewhere and based on those links you fetch certain data with that ID?
Say you fetch users information by passing the id in GET parameters and do not prepare or validate the input. Even if the id values are printed out straight from your database what prevents me to inject it?
If your links look like this for example: user.php?id=10
to fetch certain users data and show it. And then you use the id straight in the SQL query.
I can do this to get all your users data and much more: user.php?id=2 OR 1=1
Not sure if you have this vulnerability, but just to point out even if the IDs are coming from database and the field is INT it does not quarantee anything.
I personally use always prepared statements and consider it as a good practise in general. Like someone pointed out earlier what if you or someone else changes the data source to user input say 3 years after you created the code and forgets to check if it was validated/prepared.
the code is much more complicated than you can see here, sry for the bad explanation before, no point to discuss this any further, id can not be changed or declared in any way by the user.
Thank you, and I like the prepared statement also.