Do I Have to Guard Against SQL Injection If I Used a Dropdown

Do I have to guard against SQL injection if I used a dropdown?

You could do something as simple as the following example to make sure the posted size is what you expect.

$possibleOptions = array('All', 'Large', 'Medium', 'Small');

if(in_array($_POST['size'], $possibleOptions)) {
    // Expected
} else {
    // Not Expected
}

Then use mysqli_* if you are using a version of php >= 5.3.0 which you should be, to save your result. If used correctly this will help with sql injection.

Are drop down select fields vulnerable to any sort of injection

Every single element in a website can be altered by a malicious user (hidden fields, divs, styles, ajax calls, you name it...).

That said, if you're already using Prepared Statements, you shouldn't worry too much about SQL Injection because mysql already knows what statements are going to be executed.

Instead you should sanitize all the output that is being rendered in a website.

Let's say that in your form, you're asking what country I live in this way:

 <select name="country">
     <option value="Mexico">Mexico</option>
     <option value="USA">USA</option>
     <option value="Canada">Canada</option>
 </select>

but I'm a malicious user, and I use Chrome's code inspector to alter your HTML, and I select Mexico, but change its value to

<script type="text/javascript">alert("Hello World");</script>

and if you output that value in another page this way:

 Your country is: <?=$country?>

Then you'll be writing:

 Your country is:
 <script type="text/javascript">alert("Hello World")</script>

and an alert box will pop up with the text "Hello World"

What harm can I make with that you may wonder...

well I can do anything I want with that, I can steal cookies or if that value is public (say that you're displaying that value in your frontpage), then I could redirect your users to another website, change your website's content... whatever I want.

To sanitize your users' output you can use

htmlentities

That will convert, for example, the < > symbols to its respective code: < and >

Am I safe against SQL injection

In terms of your procedure you seem safe as the variable in the SP won't be expanded into code, but you can still expose yourself if you don't use a parameterized query like "SELECT * FROM sp_list_name(?);" in your appplication code. Something like "SELECT * FROM sp_list_name('$start_name');" could be subverted by a user passing a start name of "');delete from t_player where last_name NOT IN ('". So use a parameterized query or sanity check your inputs in your program.

NB: To others, please note that a variable in a stored procedure will not expand into code even if it contains a ' or ;, (excluding passing it to EXECUTE, for which you would use quote_literal, not hand-rolled replace functions) so replacing ; or ' is totally unnecessary (in the stored procedure, the application using it is a different story, of course) and would prevent you from always finding the "tl;dr" or "O'Grady" teams.

Leo Moore, Karl, LFSR Consulting: v_temp_name in the stored procedure will NOT be expanded into code in the SP (no EXECUTE), the check would need to be done n the application, not the SP (or the OP could just use a parameterized query in their app code, instead). What others are suggesting is similar to worrying about

my $bar = "foo; unlink('/etc/password');"; 
my $baz = $bar;

actually running the unlink in the absence of an eval.

Preventing SQL injection while letting the user enter their own condition at the end of the query

Allowing a user to write "their own condition at the end of a {SQL} query", can create security holes in your application which will make it prone to things like a SQL injection attack.

If you still wish to proceed, here are a couple of things to consider:

• use regular expressions to limit input to its most basic form (e.g. phone number only allows 0-9 and hyphens)
• implement your protection mechanism at the lowest level (i.e. stored procedure)
• for dynamic queries in stored procedures... never pass in field names into the stored procedure
• Never run with more privileges than necessary.
• Users that log into an application with their own login should normally only have EXEC permissions on stored procedures.
• If you use dynamic SQL, it should be confined to reading operations so that users only need SELECT permissions.
• A web site that logs into a database should not have any elevated privileges, preferably only EXEC and (maybe) SELECT permissions.
• Never let the web site log in as administrator!
• Always used parameterized statements.
• Do a code review to check for the possibility of second-order attacks.
• Ensure that error messages give nothing away about the internal architecture of the application or the database.

Again... be very, VERY careful when you implement this!

ADDITIONAL READING

• Wikipedia: SQL injection attack
• The Curse and Blessings of Dynamic SQL
• SQL Injection Attacks and Some Tips on How to Prevent Them
• Dynamic Search Conditions in T‑SQL
• Top 10 tricks to exploit SQL server systems

Do I need to escape data to protect against SQL injection when using bind_param() on MySQLi?

No, you do not need to escape data to protect against SQL injection when binding parameters.

This does not absolve you from validating said data though.

When binding parameters, there is no escaping performed (internally or otherwise). An SQL statement is prepared with parameter placeholders and values for these are passed at execution time.

The database knows what parameters are and treats them accordingly as opposed to SQL value interpolation.

Do I need to protect my ASP site from SQL Injections, when I am using parameterized queries?

Using parameterised queries is a protection aganst SQL Injection in the layer between the webserver and the database, but only if every piece of user data is used in this manner.

However, you can still get SQL injection problems from inside the database layer, so ensure that you don't call any stored procedures that themselves build queries by string interpolation.

Preventing SQL Injection in a Select X Statement

SQL Injection is a risk where uncontrolled values can become part of the query. The 'SELECT email' part of the query isn't at risk in this particular example because the values in the email column aren't part of the query.

Here's a terrible terrible example:

var query = "SELECT email FROM users WHERE username = '" + userInput + "'"

In that example the value of userInput becomes part of the query - the user can input "Blah';DROP TABLE Users" and it isn't going to be a good day. The values in the email column however aren't part of the query.

How prepared statement protect again SQL injection in below statement

Without explicitly setting a type (see PDOStatement::bindValue() for an example), it will treat the passed value as a string, so it will do this effectively:

SELECT * FROM users where id='1; DROP TABLE users;'

Btw, this would actually happen if you're using emulated prepared statements (PDO::ATTR_EMULATE_PREPARES); without this, it will send the parametrised query first followed by the actual data.

---

Related Topics