Restrict semicolon to prevent SQL injection?

I've seen that SQL injection strings are often constructed like this:

开发者_如何转开发

' ; DROP DATABASE db  --

Therefore, if I disallow the use of semicolons in my application's inputs, does this 100% prevent any SQL injection attack?

---

Use parameterized queries (or stored procedures) and avoid dynamic SQL like the plague.

I suggest using built in library functions instead of trying to write your own anti-injection code.

A naive implementation will strip out ; even if it should be used (say as part of a passed in VARCHAR or CHAR parameter, where it is legal). You will end up having to write your own SQL parser in order to accept/reject queries.

You can read here more about dynamic SQL and the problems it presents (and solves).

---

No it does not prevent sql injection attacks. Any time you're dynamically constructing SQL either in the client side, or with the EXEC inside a stored proc, you are at risk.

Parameterized queries are the preferred way to get your input into query.

---

No, it doesn’t. You just showed one example of an SQL injection. But there are far more, all depending on the context you insert the data into.

Besided that, it’s not the semicolon that causes this issue but the ' that ends the string declaration prematurely. Encode your input data properly to prevent SQL injection.

---

It MAY stop from injecting a secondary DDL statement like your drop since they'll likely create a syntax error within a select, but it won't stop statements like this:

Return more data than intended

' or 1=1

Delete with output

' or 1 in (select * from (delete someOtherTable OUTPUT DELETED.* ) a)

Injection attacks revolve around ANY manipulation of the intended SQL statement, not just termination of that statement and injecting a second statement.

Injection Attacks Come from Un-sanitized User Input

It's important, however, to not conflate a SQL injection attack entirely with dynamic/inline sql. SQL injection attacks come from UN-SANITIZED USER INPUT used in dynamic sql. You can "build" a query with no problem as long as the components of that query all come from sources that you trust. For example we use schemas to hold custom structures...

$"select * from {customSchemaName}.EmployeeExtension where id=@id and clientid=@clientId"

The above is still a parameterized query from an input point of view, but the schema name is an internal system lookup that no interface has access to.

I make the case for inline/dynamic sql here: https://dba.stackexchange.com/a/239571/9798

---

The best way to avoid SQL injection is to avoid string concatenation of user supplied data. This is best accomplished by either using stored procedures or using parameterized queries.

---

No, don't focus on semicolons. Focus on the way you put user input in the sql query - usually in quotes - and then focus on the quotes. Also don't forget when you work with regexp in sql that they need slightly different escaping procedure.

---

it will depend on a variety of things (which queries, etc). you should use prepared statements for this