Updating sql table using php

Question

I have a table (core_customer_information) and I want to create a SQL query in php that uses the variable $code and updates the activated field in my table.

$code = mysqli_real_escape_string($conn, $_GET['code']); 

$check = mysqli_query("SELECT * FROM core_customer_information WHERE activation_code='$code' AND activated='1' "); 

        if ( mysqli_num_rows($check) == 1) 
        {
            die ('The account has already been activated'); 
        }
        else 
        {
            $activate = mysqli_query("UPDATE core_customer_information SET activated='1' WHERE activation_code='$code'");
            echo ('Your account has know been activated <br>'); 
            echo $code; 
        }

First of all, I check whether the activated is equal to 1, in which case the account is classed as activated, and if not, I then create an UPDATE query to update the activated field of this class.

My problem is that the query isn't updating my table and I'm unsure where the problem is at.

I would appreciate if someone could take a look for me please.

**WARNING**: When using `mysqli` you should be using parameterized queries and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you will eventually make a mistake and create a severe [SQL injection bug](http://bobby-tables.com/). — tadman, Mar 28 '16 at 22:25
I recommend you open a new question for new questions. (For other people checking this, look at the edit logs) — GGG, Mar 29 '16 at 03:19

score 0 · Answer 1 · edited Apr 20 '16 at 02:01

It's not totally clear from your question what language you will be using to launch the SQL queries (since the only tags are sql and mysql at the moment...)

But if the language is similar to Java then you can use something similar to Java's PreparedStatement. (See https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html)

A PreparedStatement is safer than simple concatenation of dynamic variables into sql Strings.

score 0 · Accepted Answer · answered Mar 28 '16 at 21:47

I would recommend you use mysqli_real_escape_string as it escapes the string taking into account the current connection charset as stated by the page:

This function is used to create a legal SQL string that you can use in an SQL statement. The given string is encoded to an escaped SQL string, taking into account the current character set of the connection.

To prevent most mysql injection methods you should do the following:

$code = mysqli_real_escape_string($link, $_GET['code']);

If you should ever use an adapter like ADODB or some other, I'd recommend you use prepared statements and their methods of preventing SQL injection.

Updating sql table using php

2 Answers2