dynamic insert

[Anon]

hi

need help with php-mysql, dynamic insert statement

.. the code is as follows

<?
$sql = "INSERT INTO test(id) values ";
for($i=1;$i<=10;$i++) {
$sql.="($i)";
if($i!=10) {
$sql.=',';
}
}
$db_insert = mysql_query($sql,$connection)

or mysql_error();
?>

the above statement is not inserting any records ..

the insert statement i am using is correct .. also

checked with printing the sql statement .. syntax

everything is fine ... and not giving any mysql

error ...

whats going wrong?

Raghu.

[[deleted]]

Try some error checking:

if (!mysql_query($sql,$connection))
{
echo mysql_error();
}

BTW what do you need this for? it looks like you are trying to create new records for later use, but that's usually a very bad idea.

[Anon]

Hi,
I am trying to insert records in one statement which would conserve some resources of mysql.

If I do it like this

for($i=1;$i<=10;$i++) {
$sql = "INSERT INTO table(uid) VALUES('$uid[$i]')";
$execute = mysql_query($sql,$connect) or mysql_error();
}

the above code adds only first record .. remaining are ignored (ie 9 records).

Thanks,
Raghu.

[[deleted]]

Two comments:

first:
$sql = "INSERT INTO table(uid) VALUES('$uid[$i]')";

never quote numbers in SQL, and never quote variables in PHP. Change this line to this:

$sql = "INSERT INTO table(uid) VALUES(".$uid[$i].")";

second:
$execute = mysql_query($sql,$connect) or mysql_error();

makes no sense, do this instead:
if (!mysql_query($sql,$connect))
{
echo mysql_error()."<BR>".$sql."<BR>;
};

[gerardg]

never quote numbers in SQL<

For speed? The docs say to quote EVERYTHING that comes from forms.

[[deleted]]

Which docs? 🙂

Numbers are numeric, quotes force a string type, you can't insert strings into numeric fields, some databases will refuse to do this.

Quoting data from forms is usefull for security, because if someone puts an extra query inside the form-data, the entire query gets submitted:

$formdata = "1; DELETE FROM table;";
$query = "SELECT * FROM table WHERE field=".$formdata;

$query would contain:
SELECT * FROM table WHERE field=1; DELETE FROM table;

which would execute both queryes and delete all data from the table.

If the data is quoted, youd get this:

SELECT * FROM table WHERE field="1; DELETE FROM table;"

which would return no results because there are no records where 'field' contains "1; DELETE FROM table;"

However, none of the database functions in PHP can execute more than one query at a time, so including extra queries will simply cause the SQL function to fail with a 'invalid query' error.

And that error could give extra information to hackers, which means you should always do proper error-checking so you can disable the standard error-reporting in your production environment.

[gerardg]

I know 🙂.

MySQL will try to convert strings into numbers if the fields are numeric.

Didn't know php only allowed one SQL query 🙂.

These docs:

http://www.mysql.com/doc/G/e/General_security.html

Do not trust any data entered by your users. They can try to trick your code by entering special or escaped character sequences in Web forms, URLs, or whatever application you have built. Be sure that your application remains secure if a user enters something like ``; DROP DATABASE mysql;''. This is an extreme example, but large security leaks and data loss may occur as a result of hackers using similar techniques, if you do not prepare for them. Also remember to check numeric data. A common mistake is to protect only strings. Sometimes people think that if a database contains only publicly available data that it need not be protected. This is incorrect. At least denial-of-service type attacks can be performed on such databases. The simplest way to protect from this type of attack is to use apostrophes around the numeric constants: SELECT FROM table WHERE ID='234' rather than SELECT FROM table WHERE ID=234. MySQL automatically converts this string to a number and strips all non-numeric symbols from it.

[Anon]

Hi,
I figured out the problem. The problem was with Id column which is of datatype tiny int (auto increment) .. tiny int can store only numbers less than 127 .. i had already inserted 126 records .. remaining was only 1 record .. so when i was trying to insert records at 5 increments it was inserting only first record .. for remaining it was trying with 127 so the error .. unfortunately it was not showing up .. then i changed my error handler like this(thanks vincent)

---

if((!mysql_query($sql,$connection)) {
echo mysql_error();

}

still wondering why didnt it show up for

---

$insert = mysql_query($sql,$connection) or mysql_error();

then it showed up and fixed it 🙂

Raghu

[[deleted]]

Then you definately need to add more error checking, because MySQL will always give you an error about that, telling you that it 'could not insert duplicate value 127 for key 1'

[Anon]

yeah, did that already.

thanks,
raghu.