English is currently available.
SQL DISCUSSION
I am building a small web page for our lab's parts inventory. The search code builds its SQL by string concatenation, along the lines of "SELECT * FROM parts WHERE name = '" + userInput + "'", and a colleague said this is open to SQL injection. My plan was to double any single quotes in the input.
Is that enough? How do parameterized queries differ from escaping, and are there places where parameters cannot be used?
The problem is that the database receives one string and cannot tell which part was written by you and which part came from the user. If someone enters x' OR '1'='1, the statement becomes WHERE name = 'x' OR '1'='1', which is true for every row. With a different payload the attacker can append a UNION that reads other tables, or on some configurations a second statement.
Doubling quotes blocks that particular example, but it is a filter you have to apply perfectly in every query forever, and it depends on the character set and escaping rules of the specific database.
Escaping does nothing where there are no quotes to break out of. With a numeric filter built as "WHERE id = " + input, the input 5 OR 1=1 contains no quote at all and still changes the logic.
A parameterized query removes the whole class of problem. You send the statement with placeholders, SELECT * FROM parts WHERE name = ? AND bin = ? (some drivers use named markers such as @name or :name), and pass the values separately. The statement is parsed with the placeholders in place, so a value can only ever be data; whatever it contains, it is compared as a string and never read as SQL.
Parameters can stand only where a value is allowed. They cannot replace table names, column names, the ASC/DESC keyword or other pieces of syntax. For a user-selectable sort column, map the input to a fixed list in code, for example accept only name, bin or qty and reject everything else, then insert the name you chose rather than the text the user sent.
For an IN list, generate one placeholder per value (IN (?, ?, ?) for three values) and bind each one. With LIKE, the value is safe from injection but % and _ typed by the user still act as wildcards, so escape those if they should be literal.
Two further layers are worth having. A stored procedure is only safe if it uses its parameters directly; one that concatenates them into a string and executes it is injectable again. And the account the web page connects with should have only the rights it needs, typically SELECT, INSERT and UPDATE on the application's own tables, not ownership of the database, so a mistake somewhere has limited reach.
Validate input as well (a quantity should parse as an integer before it gets anywhere near a query), but treat validation as an extra check, not as the defence.
