1

Quick and dirty: I would like to check if a specific row already exists using only INSERT rights.

Explanation
I have restricted my database account to INSERT rights for security purposes. I realize there are other steps to take after mysqli_real_escape_string but being a rookie to both PHP and MySQL, I thought this to be the best catch-all at thwarting injections.

After submit, the form does its validation checks and attempts to create the row and either passes or catches a 1062 error and says the username already exists.

I now want to implement a preemptive AJAX call using the same DB account. I thought about issuing a dummy update statement (that makes no changes) to check for a duplicate, but evidently that fails without SELECT rights. SELECT right is the main one I was attempting to avoid; is this being naive and what would be the proper way to go about this?

Gary
  • 13,303
  • 18
  • 49
  • 71
  • 1
    The *correct* way to [prevent SQL injection](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php) is to use placeholders. It's simple and it works. While limiting access is an additional layer, just use the basic tested access patterns (i.e. placeholders) correctly and many potential issues are mitigated. Now, one still has to *use* the data correctly (e.g. encode for JSON or HTML output), but that's not "SQL injection". –  Mar 16 '13 at 02:55
  • Also, I don't see how giving INSERT (without SELECT) prevents the general case of SQL injection, even though it might be able to prevent some direct query ability .. what if the ability to alter the schema is not locked down as well? –  Mar 16 '13 at 02:59
  • My thinking was in the case of an injection the worst they could do was create new users. To clarify, the DB user only has INSERT rights. – Gary Mar 16 '13 at 03:01
  • "Worst" .. use proper placeholder/binding techniques. Limiting access is valid for other reasons, but not to "prevent SQL injections". The code that accesses SQL either *uses placeholders* or it is *doing it wrong*. There is no middle ground. There is no forgetting to escape input. –  Mar 16 '13 at 03:03

1 Answers1

0

You can use INSERT IGNORE INTO table

see http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html

There is a question very similar to yours: How to 'insert if not exists' in MySQL?

Community
  • 1
  • 1