SQL Injection

[iago]

Basically, how does SQL Injection work?  I don't know much about SQL, but I am seeing an awful lot of unchecked inserts into a table here, and I want to make sure this can't happen.

They are filtering out apostrophe (') and newlines.  Is this enough?

Thanks!
-iago

[Grok]

No it is not enough.  You can still insert queries into a string that gets executed and returns a string, so that the SQL engine does not return an error, yet performs the injected actions.

There are web pages out there describing how to write injection-proof SQL, so will defer to those for examples.

[iago]

No it is not enough.  You can still insert queries into a string that gets executed and returns a string, so that the SQL engine does not return an error, yet performs the injected actions.

There are web pages out there describing how to write injection-proof SQL, so will defer to those for examples.

Ok, I'll look into this harder next week, then.  Right now, the only thing I've looked at regarding the database is the stuff being inserted.  I suspect (but don't know for sure) that the querying is safe (since it uses hardcoded/constant queries, for the most part), though.

Is there also a hazard with Inserts?  

I will definately google it monday, but in the meantime if anybody knows offhand any good resources I woudln't mind getting a headstart

[Grok]

Show me the hardcoded queries and inserts.

[iago]

Show me the hardcoded queries and inserts.

Sure, on Monday.  The code's at work.

[iago]

Sorry I didn't get back to this, but this is what happened.

I was looking at their Databases, and realized that, the way they had them set up, was completely worthless.  It's supposed to be an electronic-handin system for schools, but they only had 2 tables, one for a description of the assignment, which included course number, section, duedate, etc., and one for external links/files.  This made it impossible to improve on it, or to search for teachers/students/anything like that.

So bottom line is, I redid the databases and I'm rewriting the accessor functions.  And all accessors pass through a central execute routine (unlike theirs where the programmer had to remember to call verifySQL() on each statement) which does the following checks:
Only 1 semicolon: at the end
No apostrophe(') or backslash (\) or newline (\n) allowed within user-entered strings.  Is there anything else I should worry about while I'm coding?

<edit> also, we're using mysql Ver 11.15 Distrib 3.23.43 if that matters

[Grok]

Easier is renaming the tables, then create two views of the same name.  Let the existing application hit the views.

Then, you can restructure the data model in the background.  As you make each change to a table, update the stored procedures which make up the views.  To the application, no changes are happening server-side.

As far as quotes and other characters, you should not allow any user-entered text to be passed as a SQL-string to your database.  Instead, write parameterized queries using specific data types.  Even if the user passes an otherwise dangerous string to your parameter, it will not be executed as code, but rather as an argment to a compiled procedure.  Thus, it cannot get executed, but only treated as the data it is.

I will repeat:  NEVER TRUST USE INPUT.

[iago]

Easier is renaming the tables, then create two views of the same name.  Let the existing application hit the views.

Then, you can restructure the data model in the background.  As you make each change to a table, update the stored procedures which make up the views.  To the application, no changes are happening server-side.

As far as quotes and other characters, you should not allow any user-entered text to be passed as a SQL-string to your database.  Instead, write parameterized queries using specific data types.  Even if the user passes an otherwise dangerous string to your parameter, it will not be executed as code, but rather as an argment to a compiled procedure.  Thus, it cannot get executed, but only treated as the data it is.

I will repeat:  NEVER TRUST USE INPUT.

The databases we have are worthless as it stands, and I'm just redoing them from the gound up.  Right now it's not deployed on any server except the test, so playing with the database isn't hurting anything.

So far there's no direct accesses, but, let's say the user wants to get a list of assigments from their teacher.  I have to do a query something like this:
SELECT * FROM Assignments WHERE Course=[the user's entered data]
How can I restructure that so they don't have to enter the data specifically?

One way I was thinking is just do a SELECT * FROM Assignments, then go through them all and grab the ones I want.  Although that's less efficient, it would be much safer.

Thoughts?

[Grok]

Create a server cursor.
Open the cursor.
Compare records for columns matching your criteria.
Build the result set.
If asynchronous, send result to caller.
Fetch new row until exit condition met.
Close the cursor.