MySQL error when query is wrong VS when input is wrong

Question

Is there a way to distinguish between error messages returned by MySQL when the query is formed incorrectly VS when the user input is wrongly entered?

For example:

//WRONG QUERY 
 try {    
    INSERT into table_name ('clm1','clm2') values('val1','val2'); //BAD query
   }
 catch (PDOException $e) {
        echo $e->getMessage();    //display the error message returned
 }

VS.

//wrong user input

try {
   INSERT INTO table_name ('unique_clm1') value('duplicate_value_from_user');
 }

     catch (PDOException $e) {
            echo $e->getMessage();    //display the error message returned
     }

REASON:

If there is an error in query written by the developer; the error needs to be displayed as a raw MySQL error for debugging......while a bad user entry needs to be shown as an error message to the user without disrupting the normal flow of code.....

User input should be validated and sanitized before it reaches the database. In other words, bad user input should *ideally* never be a factor when you run db queries. — Captain Hypertext, Nov 03 '16 at 01:20
I am not sure it's a good idea to rely on the DB for user input validation. You should do it in your code and warn the user, before you even get to the DB INSERT — Galz, Nov 03 '16 at 01:20
Input validation. Then tell the user what is wrong and to try again. — Irvin, Nov 03 '16 at 01:20
Also, make sure you use prepared statements and parameterized queries when plugging user input to SQL queries. Read this: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Galz, Nov 03 '16 at 01:22
@Galz I SPECIFICALLY mentioned DUPLICATE ENTRY into the table....this is the only scenario where the user input may be entered without sanitation.....since the DB can tell if there is a duplication. Obviously I am using prepared statements...the above 'code' is for illustration only! — Nikster2014, Nov 03 '16 at 01:25
My question breaks down into a very simple one: Is there a way to distinguish MySQL error messages caused by a wrong query Vs. an error message caused by wrong user input (such as a duplicate value for a unique column) — Nikster2014, Nov 03 '16 at 01:26
You'll need to test the message you get back from PDO and decide how to handle it. — tadman, Nov 03 '16 at 01:26

score 1 · Accepted Answer · answered Nov 03 '16 at 01:46

You have a few options:

1. Use INSERT IGNORE

Using IGNORE means that the query will not fail on duplicate key. So failure means that there is a problem in the query. To know if there was a duplicate all you need is to get the number of affected rows. 0 means nothing was inserted (so there was a duplicate value). 1 means a row was inserted.

2. Use tests to catch bad queries

Bad queries in the code stay bad until fixed. They will not be OK for some users and then go bad for others. So if you have some tests in place, the tests will catch BUGS while the error handling in your code will catch "Something went wrong" that might be a duplicate entry, DB connectivity etc.

3. Read the error message

In your code, you can read the content of $e, and handle accordingly. If $e contains duplicate key or the relevant error code, notify the user. Otherwise log the error as something else (not necessarily bad query - DB connectivity is another good example for an error that can come up in runtime).

MySQL error when query is wrong VS when input is wrong

1 Answers1