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.
That's a horrible example. SQL injection IS the fault of the programmer, not SQL itself. SQL injection is achieved by adding extra code to a query, which is only possible when a programmer allows inputs that can contain code to be concatenated directly into a query. Here's an example:
query = "SELECT * FROM USERS WHERE NAME = '" + userinput + "'";
exec(query)
This input can be given:
' OR 1=1--
To make the application show the entire list of users. If this programmer used parameter binding, which is supported by PostgreSQL, MySQL, SQLite, and any other SQL platform you can think of, then SQL injection wouldn't be an issue. They could simply do something like this:
query = "SELECT * FROM USERS WHERE NAME = @:USER";
statement = prepare(query, "USER", userinput)
exec(statement)
Just because you don't know the right way to do something securely, doesn't mean it's not there. But you're right, no security expert fixed this problem. It was fixed by the library designers of these SQL platforms. Security experts just charge you by the hour to teach you that you're unfamiliar with the existing security mechanisms inside of these platforms.
Also, just to be pedantic, I'll point out that a type system wouldn't change how SQL injects currently work, lol, no clue how you think that's the case, but I wouldn't put it past you at this point.
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?
Instead of policing every line of code you can also mitigate SQL injection by restricting the access of the database handle the user-facing queries are using
* All read-only queries use a read-only (SELECT only) database account. Injecting; DROP TABLES or INSERT, UPDATE, etc (DML commands) just error out.
* User accounts and logins are stored in a different database, so only the code responsible for login and registration can access those tables/database.
* All queries should use prepared statements which effectively treats all injected text as just text rather than database commands.
> The parallel for SQL injection might be if you’re building a data charting tool that allows users to enter arbitrary SQL queries. You might want to allow them to enter SELECT queries but not data-modification queries. In these cases you’re best off using a proper SQL parser [...] to ensure it’s a well-formed SELECT query – but doing this correctly is not trivial, so be sure to get security review.
If you are ever in this situation, you should actually use a dedicated read-only user that can only access the relevant data. If you need to hide columns, use views. Trying to parse SQL can easily go very wrong, especially when someone (ab-)uses the edge cases of your DB.
SQL injection is a problem with SQL, which is similar to problems with HTML. SQL was created as human-friendly query languages, it wasn't created to be built from strings in a programming language. Proper database API should be just a bunch of query builder calls and with this API SQL-injection is not possible.
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.
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?
"SQL injection attacks can allow hackers to execute arbitrary SQL commands on your database through your Web site. To avoid these attacks, every piece of data supplied by a user on a Web form, through HTTP Post or CGI parameters, or other means, must be validated to not contain information that is not expected."
This is just silly. There is a really simple way to prevent all SQL injection - use parameterized sql.
Database drivers for mabny programming languages have something like Java's PreparedStatement which allows you to compile the SQL query together with code, before the application is ran. Whatever input is provided later by the user cannot result in SQL injection, because it is not parsed/compiled at all. So yes, SQL injection is a solved problem, it's up to you whether you know about/use the solution.
You shouldn't trust it just because of your UI. With developer tools, regardless of your UI, it would be trivially easy for a malicious user to send any arbitrary SQL query. This would allow them to retrieve sensitive data or modify/delete records from your production database. You really should only build this query server-side, and, as the OP is using PHP, I should also add the caveat that they should use PDO.
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.
Yes, you can write a Query type that is vulnerable to SQL injection, if you want to.
But if you write a secure version, you only have to write it once. You only have to maintain it in one place. You only need to test it in one place. And if you forget to use your secure Query type, anywhere else in your code, the compiler will yell at you. It's a significant advantage.
This is easier to see in a language with a rich, flexible and expressive type system than it is in Java. The writer of the original article used Haskell for a reason.
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.
reply