Forgive my ignorance, but if you're using prepared statements for all your SQL queries using user input, aren't you by definition safe from any kind of injection?
sql injection is commonly caused by combining your query with its related data parameters in unsafe ways. you are emitting raw user input you received to another program, the database, it's your responsibility to give this to the DB safely.
you still have to be careful, and when you follow all the right best practices you can safely ingest raw user input.
I've worked at a company that escaped user input before inserting into the DB. it's a horrible nightmare I don't think anyone should have to experience.
No, really - I'm certain there aren't any in my code. SQL injections are extremely easy to protect against if you know how it works. There might however be other vulnerabilities.
I'm sorry to bring the bad news, but SQL injection vulnerabilities are not a problem of the combination of PHP and MySQL. You can have the same problems with most other combinations of a scripting language and a backend database, for example ASP and Microsoft SQL Server.
The true lesson is: Don't assume user's input is ok for SQL queries. All input is evil!
As a side note, this is how many SQL injection attacks happen too. You almost never want unfiltered user input to directly interact with your system. A while back, I did an episode on how SQL injection can lead to code execution by using unfiltered user input on a LAMP stack. See it @ http://sysadmincasts.com/episodes/21-anatomy-of-a-sql-inject...
Would you be so kind to explain me the attack vector if the user input is never possibly treated as part of the code?
What I came up with is this: user name is stored in the database, and some new junior developer in a large team reads it in the backend code, and immediately plugs into another SQL query using string concatenation. BOOM!
But on the other hand, the very same junior developer can forget to sanitize the inputs before storing them (or do it incorrectly), so there.
No, the point is that people do write code that is unsafe. A cursory glance at recent stack overflow questions tagged with "node.js" and "sql" shows many questions and answers with SQL Injection vulnerabilities.
By providing an API that makes it virtually impossible to create an SQL Injection vulnerability, we can allow novices to write code safely. Once you know what the `sql` tag is doing, it's really easy to review the code and be confident it isn't vulnerable.
Is this serious? It seems to prevent SQL injection by only allowing statically defined strings to be interpolated. So basically not allowing any kind of dynamic or user input.
If the user is supposed to enter arbitrary SQL queries, then it is already allowed. (SQL injection should still be prevented when using forms that do not say you can use arbitrary SQL code, though, even if it is the same database and even if it is read-only and all data is public. This isn't because of any kind of vulnerabilities, but merely to avoid bugs in the form in case a user enters something unexpected.) However, you may add some access restrictions to the database to ensure that it cannot be written (how to do that depends what database you are using; with SQLite, there is an authorizer hook, defensive mode, read-only mode (when opening the file), file permissions in the operating system, progress callbacks, etc). Also, if you are allowing users to enter arbitrary SQL queries, please link to the documentation so that the user is aware what variant of SQL is in use; there are different variants of SQL which have a few differences from each other. Being able to enter arbitrary SQL queries is helpful, but this should be done properly, which is by documenting this feature properly.
In other words: If the only query form is for the user to enter their own SQL query, then SQL injection is irrelevant. If it has such a query form but also has an additional form that does not use the user's own SQL query, then the additional form without the user's own SQL query should be protected against SQL injections, by passing parameters properly.
But there is another possibility too, which is to use remote virtual tables (which could then be accessed using SQLite on the user's own computer, with a suitable extension installed (I invented a "remote virtual table protocol", although possibly improvements could be made)). This allows the user to do such things as use JOIN queries with other data, create temporary tables, do their own formatting, etc.
If I came onto your project I'd be trying to fix that SQL injection too. Why don't you add a comment explaining why you know it's safe and why you aren't using the standard way of escaping variables in SQL?
This isn't a search for SQL injection, its a search for a couple things that you often find in older PHP code that is generally hacked together and likely to have SQL injection vulnerabilities for historical and cultural reasons. However it's perfectly easy to avoid SQL injection even using these things.
$id = mysql_real_escape_string($_GET['id']);
$res = mysql_query("SELECT foo FROM bar WHERE id='$id'");
That may be ugly, but it's bulletproof regarding injection.
Looking at that list of SQL injection attack techniques, I don't see the threat.
Or: Yes, I'm building a Web site, and the server will be running SQL Server.
Some of my Web pages have users enter data in text boxes. So, they could enter a SQL command in a text box.
Then when the Web page is returned to my Web server as a 'post back', my software reads the data in the text boxes.
I have the page built, have code for extracting the data from the text boxes and putting it in, say, string variables in Visual Basic .NET, and now am writing the code for looking at the data in the strings from the text boxes.
So far, I see no threat.
So, I intend to look at the data in the strings and see if it looks anything like a SQL command. Anything that looks like a SQL command will get rejected as bad data and not get near my SQL Server database. Maybe I will write the bad data to my log file.
So, I'm checking the input data from the users. I am assuming that the input data could be anything at all until my code establishes otherwise.
Doesn't everyone do such checking?
With such checking, where is the threat of 'SQL injection'?
If SQL injection is a threat, then why? Just from people not checking input data from users? Some other reason?
Is that not a dangerous way to prevent SQL injection? What happens if someone calls it with parentheses? Will that throw an error or will it bypass the SQL injection prevention?
reply