Problem with MYSQL database, values are not inserted

Question

I am trying to insert values in database and values are not being inserted, here is the code i have:

    $user_name = "username";
    $password = "password";
    $database = "database";
    $server = "localhost";
    $db_handle = mysql_connect($server, $user_name, $password);
    $db_found = mysql_select_db($database, $db_handle);

    if ($db_found) {

    $SQL = 'INSERT INTO table (anInt, DomainName, URL, Rank, PageRank, Google, Bing, Boss, IndexedPage, Backlinks) VALUES ($anInt, $Domain, $URL, $Rank, $Pagerank, $Google, $Bing, $Yahoo, $Pages, $backlinks)';
    $result = mysql_query($SQL);
    mysql_close($db_handle);

    print "Records added to the database";

it is printing that records added to the database but when looking at the database nothing is being added. some of the values are doubles, text, and ints. Is there anyway to debug this? I will be adding more information to the post if someone asks me to.

and of course I have an else statement i just thought it is not relevant since it is telling me that records are added.

Run away - we're under SQL injection attack! en.wikipedia.org/wiki/SQL_injection — Dominic Rodger
– Dominic Rodger, Commented Feb 23, 2010 at 11:42
I think we need a bit more code: how are you declaring the variables that are being inserted? Also, your if block is incomplete—is this because you have missed out some code when pasting or is it like that in the actual code? — Mark Bell
– Mark Bell, Commented Feb 23, 2010 at 11:47
Sorry, ignore the bit about the 'if' block—I just read the end sentence of your answer again. — Mark Bell
– Mark Bell, Commented Feb 23, 2010 at 11:48

Pascal MARTIN · Accepted Answer · 2010-02-23 11:53:28Z

4

First of all, you should escape the string values you are passing into the SQL query, using mysql_real_escape_string.

Then, you should add quotes, in your SQL query, arround the fields that are meant to contain strings.

I don't really know which fields are integers and which fields are strings, but you should be using something like this to build your SQL query :

// Escape the string data, and make sure integer really contain integers
$anInt = intval($anInt);
$Domain = mysql_real_escape_string($Domain);
$URL = mysql_real_escape_string($URL);
$Rank = intval($Rank);
$Pagerank =  = intval($Pagerank);
$Google = intval($Google);
$Bing = intval($Bing);
$Yahoo = intval($Yahoo);
$Pages = intval($Pages);
$backlinks = intval($backlinks );

// Build the SQL query, using the "safe" variables
$SQL = 'INSERT INTO table (anInt, DomainName, URL, Rank, PageRank, Google, Bing, Boss, IndexedPage, Backlinks) 
        VALUES ($anInt, '$Domain', '$URL', $Rank, $Pagerank, $Google, $Bing, $Yahoo, $Pages, $backlinks)';

This is supposing that only DomainName and URL are meant to contain strings -- you might have to use mysql_real_escape_string and add quotes arround the values for some other fields too, if needed.

Then, you should take a look at the return value of mysql_query : for an insert query, in case of an error, it'll return false.

Here, if your $result variable is false, you should use mysql_error and mysql_errno : they'll allow you to know what error happened -- it will help detecting errors in your SQL query, for instance.

If this doesn't solve the problem, you should try outputting the SQL query, and run it using something like phpMyAdmin, to make sure it's OK.

edited Feb 23, 2010 at 11:53

answered Feb 23, 2010 at 11:46

Pascal MARTIN

402k82 gold badges665 silver badges666 bronze badges

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

2 Comments

user220755 Over a year ago

what do we use for doubles? (i mean instead of intval) ? thanks!

Pascal MARTIN Over a year ago

For doubles, I suppose floatval (see php.net/floatval ) would do the trick -- the idea, again, being to be sure that the variable contains what you expect it to contain.

extraneon · Accepted Answer · 2010-02-23 11:46:37Z

2

I am no PHP expert, but I have 2 remarks.

You don't check the error (perhaps with mysql_errno()) so you don't know whether the records were added
I think the values, if they are strings, should be given like

'$Domain'

that is, escaped with ' characters.

better would be, of course, using something like

$sql = sprintf("INSERT ... VALUES(%d, '%s', '%s',...)", 
               $anInt, mysql_real_escape_string($Domain), ...);

if you insert user-supplied input.

answered Feb 23, 2010 at 11:46

extraneon

24.1k2 gold badges51 silver badges52 bronze badges

Comments

Andomar · Accepted Answer · 2010-02-23 11:47:51Z

2

You could examine the $result:

$result = mysql_query($query);
if (!$result) {
    print "An error occured: " . mysql_error() . "\n";
}

My guess is that you're passing a string without quotes, like:

VALUES (Hello)

where you should pass it like:

VALUES ('Hello')

Like the commenter said, if the user can control these strings, you are open to an SQL Injection attack. You can prevent that attack by escaping the strings, for example:

$query = sprintf("INSERT INTO table (DomainName) VALUES ('%s')",
        mysql_real_escape_string($domain_name));

answered Feb 23, 2010 at 11:47

Andomar

239k55 gold badges387 silver badges412 bronze badges

Comments

poke · Accepted Answer · 2010-02-23 11:48:49Z

2

In SQL queries, you need to enquote strings correctly, or it will produce an error. So all your variables that are used to store non-int or non-boolean values in the database need quotes around the values.

Additionally you should make sure that SQL injections are not a problem by escaping all values with mysql_real_escape_string first.

answered Feb 23, 2010 at 11:48

poke

392k80 gold badges596 silver badges632 bronze badges

Comments

VolkerK · Accepted Answer · 2010-02-23 11:49:41Z

2

Apart from sql injections your error handling is not complete...

if (!$db_found) {
  echo "datbase not found.";
}
else {
  $SQL = 'INSERT INTO
      table
      (...)
    VALUES
      (...)
  ';
  $result = mysql_query($SQL, $db_handle);
  if ( !$result ) {
    echo "error: ", mysql_error($db_handle);
  }
  else {
    print "Records added to the database";
  }
}
mysql_close($db_handle);

In case a query causes an error mysql_query() return FALSE and mysql_error() will tell you more about the error.

answered Feb 23, 2010 at 11:49

VolkerK

96.3k20 gold badges169 silver badges232 bronze badges

Comments

Derek Organ · Accepted Answer · 2010-02-23 11:46:07Z

1

Well there are security issues with the code but to address one problem

you are not enclosing your string values in quotes in the SQL statement.

answered Feb 23, 2010 at 11:46

Derek Organ

8,49317 gold badges60 silver badges75 bronze badges

Comments

Alan Plum · Accepted Answer · 2010-02-23 12:04:20Z

First of all, please regard everybody else's advice on safe database handling and avoiding injection.

The reason your query isn't doing anything is probably that you enclosed the string in single quotes. In PHP single quotes enforce the string to be literal. Unlike when using double quotes, variables will NOT be substituted. So '$foo' represents the sequence of characters '$'.'f'.'o'.'o'. "$foo" on the other hand represents the sequence of characters of whatever the variable $foo contains at the time of the string's definition.

You can use mysql_error() to catch most problems with MySQL. Even if the message isn't helping you, you at least know whether the query was parsed properly, i.e. on which end of the connection the problem lies.

Collectives™ on Stack Overflow

Problem with MYSQL database, values are not inserted

7 Answers 7

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related