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. — HereForGoodTimesOnly
– HereForGoodTimesOnly, Commented Nov 6, 2013 at 12:09
Bookmark this => stackoverflow.com/q/60174/1415724 and this => owasp.org/index.php/Top_10_2013-Top_10 — Funk Forty Niner
– Funk Forty Niner, Commented Nov 6, 2013 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
– ToBe, Commented Nov 6, 2013 at 15:20

SQB · Accepted Answer · 2013-11-06 14:45:42Z

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 6, 2013 at 14:45

answered Nov 6, 2013 at 11:46

SQB

4,1262 gold badges34 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JimmyB Over a year ago

For MySQL see for example this.

HereForGoodTimesOnly Over a year ago

@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

neilco · Accepted Answer · 2013-11-06 11:53:59Z

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 6, 2013 at 11:53

neilco

8,0022 gold badges39 silver badges41 bronze badges

answered Nov 6, 2013 at 11:40

sanjeet sharma

237 bronze badges

Collectives™ on Stack Overflow

insert/update and also avoiding sql injection

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related