As I know,prepared statements are used when data are sent to the server…from a form for example.
The means UPDATE and INSERT statements.
So…does it make any sense using prepared statements with SELECT command.
Cause in the PHP manual i saw such an example.
http://php.net/manual/en/mysqli.prepare.php
Of course, it makes sense to use anywhere, even in select statements. The fact is that prepared statement are automatically quoted to ensure safety and prevent SQL injection risk. Security is very important for a PHP application, especially since the language by itself does not do anything like that for you, you need to keep in mind yourself.
yes…but when selecting data from a table there is no SQL injection risk.
Unless I am wrong in which case I would be thankful if you could explain that to me.
I suppose as long as all the parameters are NOT and can NEVER BE coming from any user input you could get away with not using prepared statements as far as for security concerns.
And if you aren’t concerned about data integrity in the event of a mess up happening…
There are two reasons to use them in EVERY case:
Always use prepared statements when you can. It’s a good habit to get into.
correct me if I am wrong.
When selecting data from a table…these data by definition do not come from user input.
It depends on what’s in the WHERE eg.
SELECT user_id FROM member_table WHERE member_name LIKE $_POST['name']
is using user input
but something like
SELECT user_id, member_name FROM member_table WHERE join_date < NOW()
isn’t.
In any case, what would be your argument for ever not using them?
yes but the data in a table…even if they come from user input….they have been filtered out already.
That is the reason I am saying that there is no sql injection risk in such case.
So you have no reason to not do it. And a lot of reasons to do it.
Ever hear of “Bobby Tables”?
I would like an answer to what I said…read again my post please.
Specifically when I am saying that…
data have been already filtered out when in table
You’re making an enormous assumption that filtering will remove any characters special to SQL. Then when building your SQL statements, you have to know which user inputs have been filtered, and what they were filtered for. Now your SQL is more complex than it needs to be and you’re much more likely to make a mistake.
But if you always use prepared statements, then you don’t need to know or care whether a value was filtered, nor how it was filtered. Even unfiltered user input would be safe from injection. Your code becomes simpler, and you’re tremendously less likely to make a mistake.
I got the answer what I wanted…thanks.
I will use prepared statements in SELECT statements also.
Τhe only problem with prepared statements is that they are comprised of more code
than simple statements.
I think part of the confusion may be your misunderstanding of the meaning of “injection” here.
If it meant “into the database”, then it would follow that because a SELECT is getting data out of the database, nothing is being put into it.
It is more accurate to think of “injection” as meaning into the query
I cannot quite understand the above,can you explain it a little?
SELECT user_id FROM member_table WHERE member_name LIKE $_POST[‘name’]
Using his example - injection would be about what the user might have input into the ‘name’ field - the malicious user is not trying to insert bad data into your database, they’re trying to hijack your query - and make the query say something else. For example, they might make their “name” into more SQL query. In another case, this could be terrible. In some cases, it wouldn’t do much.
So for example I might input “WHERE user = 1” into a form as my “name” or my “address”. I’d consider how the query might be set up, consider how I might could jack that query to perform in a different way than intended, and then go from there.
I’m by no means a security and SQL expert, we have several of those here who may chime in and do a better job of explaining (Probably @Mitteneague himself will answer you since you were talking to him) I just figured I’d give you my two cents worth.
TL;DR - Injection can occur without the user actually being interested in inserting bad values. The two can be concurrent, but they could also be separate things entirely.
I understand now better why there is injection danger even in SELECT statements.
But as I understand…in a SELECT statement such as this(meaning is depends on the SELECT statement):
SELECT appointments.Bookfrom,appointments.apID,staffID,appointments.apps_origin,
from appointments,users
WHERE users.email="'.$email.'"
There is no injection danger…correct me if I am wrong
Where is the value for $email coming from? Any use submitted data, via $_GET, $_POST, $_FILES, $_COOKIE or $_REQUEST should always be considered to be dangerous until it has been sanitized and escaped. For escaping prepared statements should ALWAYS be used.
Also there is no valid reason for any application to use $_REQUEST as if the value is coming from a query string (the URL) then $_GET should always be used. If the value is coming from a form then $_POST should always be used.
I want to say two things.
The $email comes from $_SESSION…so I do not know if there is a danger in this case.
If I am going to use prepared statements(not in SELECT only) but anywhere…is there any point in using real_escape_string?
Not if you always use prepare statements.
Preventing injection is just a side effect of using prepare statements - the real benefit of prepare statements is that you only need to prepare the statement once and can then bind different data to it when running it multiple times. That improves the efficiency of the database calls. Not something you might need to do all that often in simple web pages but still worth knowing about.
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.