insert/update and also avoiding sql injection

Question

I want to check if somethign exist in the database before I update/insert. This is a two question in one but similar.

This is how I do it.

$p->main .= '<td><input type="text" name="value"  id="value"  size="12" maxlength="50" />';

    $statement = $conn->prepare("SELECT year, month, name table_test WHERE MLOV_year= :Year 
                 AND month= month AND name= :name");

                $bind = array(
                                'year' => $year,
                                'month'   = > $month,
                'name' = > $name
                );


                $statement->tyu_exec_sql_bind($conn,$statement, $bind );

                 if ( false === $statement->fetch()) {
                               // I will run an insert statement here.
                }



    $p->main .= '</td>';
    $p->main .= '</tr>';

Question This is my first time trying it this way and I was wondering if that is right? also if that is security?

@perdeu this is what I normally use for years but this code i am write was done by someone else which connect to oracle database and it works similar to mysqli. I am trying to avoid someone trying to use an sql injection. — PHPnoob, Nov 06 '13 at 12:09
Bookmark this => http://stackoverflow.com/q/60174/1415724 and this => https://www.owasp.org/index.php/Top_10_2013-Top_10 — Funk Forty Niner, Nov 06 '13 at 14:32
What kind of Database library do you use? What object is "$statement" ? Depending on the used library, a bind can range from anything to just inserting variables (Unsafe) to filtering and mapping types (safe). — ToBe, Nov 06 '13 at 15:20

SQB · Answer 1 · 2013-11-06T14:45:42.857

1

Depending on the database, you might be able to use MERGE, which does exactly that, inserting a record if it doesn't exist and updating it if it does, atomically.

This is preferrable to any check you do yourself, since that makes it a two step process. In theory, someone else could insert the record between your check and your insert, making your insert fail.

Use prepared statements to prevent SQL injection. You did that in your first bit of code, why not in your second?

And a tip, don't call your variable 'delete' when it contains an update statement, that confuses the next person to read this.

edited Nov 06 '13 at 14:45

answered Nov 06 '13 at 11:46

SQB

3,926
2
28
49

For MySQL see for example [this](http://stackoverflow.com/questions/4205181/insert-to-table-or-update-if-exists-mysql). – JimmyB Nov 06 '13 at 11:54
@SQB. I am trying to prevent sql inject because of the `'". ."'` from my understand someone can run an sql injection. I want to avoid this before I run the check insert where I am running the insert statement and also when I am running an update. Hope that makes sense – PHPnoob Nov 06 '13 at 12:16

score 0 · Answer 2 · edited Nov 06 '13 at 11:53

0

First you need to check that the value exists in database, which means you need to query for it, and then take the decision of update or delete.

You could also set a unique key constraint in a table which would prevent inserting duplicate values.

edited Nov 06 '13 at 11:53

neilco

7,964
2
36
41

answered Nov 06 '13 at 11:40

sanjeet sharma

23
7

insert/update and also avoiding sql injection

2 Answers2