Creating an sql database to store an ip adress

[keyboard]

Hi everyone, I am trying to make a database that will store someones ip adress and name.

Here is the code



<?php
// Make a MySQL Connection
mysql_connect("********", "**********", "********") or die(mysql_error());
mysql_select_db("*********") or die(mysql_error());

// Create a MySQL table in the selected database
mysql_query("CREATE TABLE ipadress(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(30),
ipadress VARCHAR(30)")
or die(mysql_error());

echo "Table Created!";

?>


There is an error when I try and run it. Any help is appreciated.

[traq]

missed the closing parentheses


"CREATE TABLE ipadress(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(30),
ipadress VARCHAR(30))"

if it still won't work, post the error you're receiving.

[keyboard]

<?php
// Make a MySQL Connection
mysql_connect("****", "*****", "*****") or die(mysql_error());
mysql_select_db("*****") or die(mysql_error());

// Create a MySQL table in the selected database
mysql_query("CREATE TABLE ipadress(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(30),
ipadress VARCHAR(30))"
or die(mysql_error())
?>


Thats the code. I did what you said but now its coming up with a new error.


Parse error: syntax error, unexpected ';' in /home1/keyboard/public_html/databasing.php on line 13


I counted the lines and it it the very last one ( The ?>) There isn't even a semi colon in that line. I'm very confused. Any help would be appreciated.

[traq]

just more typos

if you indent your code more regularly, and/or use a text editor with syntax highlighting, it will be easier to track what's closed and what's unclosed.

the line php reports an error is not always the line where the error occurred: it's just the line where php decided it couldn't recover.
don't copy-and-paste this.
for clarity, I did not use php-style comments.

mysql_query(
"CREATE TABLE ipadress(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(30),
ipadress VARCHAR(30) <--that's where the length argument for the ipadress column ends
)" <--that's where the ipadress table arguments end
) <--that's where the mysql_query argument ends (was missing)
or die(mysql_error()); <-- semicolon was missing

[keyboard]

Yep changed that and it worked. got another question. Is there any reason this wouldn't work?


<?php
// Make a MySQL Connection
mysql_connect("localhost", "****", "****") or die(mysql_error());
mysql_select_db("****") or die(mysql_error());

$cheese = $_POST['name'];
$cheesy = $_SERVER['REMOTE_ADDR']
mysql_query("INSERT INTO ipadress
(name, ipadress) VALUES('$cheese', '$cheesy' ) ")
or die(mysql_error());


echo "Data Inserted!";

?>

[traq]

you need to validate your user input. (I'm assuming this is related to your other thread - you should consider having only one thread per topic, it's a lot easier to follow and gives a clearer picture of what you're doing.)

assuming you're validating $_POST['name'] against ^[- 'A-Za-z]+$, then you still need to use mysql_real_escape_string() (since you're allowing apostrophes). If you validate in a way that excludes dangerous characters, then you can safely skip mysql_real_escape_string(), but doing it anyway will never cause any harm - better safe than sorry!

also, while not a critical problem, you shouldn't be using die() for error handling.
// instead of dying:
mysql_query("INSERT blah blah") or die();
// you should deal with the error:
$result = mysql_query("INSERT blah blah");
if(mysql_affected_rows() !== 1){
/* mysql error and/or INSERT failed, give error message. */
}

[keyboard]

I tried to run the script. (haven't put in the validation) and it wouldn't work. Came up with this error

Parse error: syntax error, unexpected T_STRING in /home1/keyboard/public_html/databasingthesecond.php on line 8


any help would be appreciated.

[traq]

<?php
// Make a MySQL Connection
mysql_connect("localhost", "****", "****") or die(mysql_error());
mysql_select_db("****") or die(mysql_error());

$cheese = mysql_real_escape_string($_POST['name']); // <-- even if you validate the name, do at least this
$cheesy = $_SERVER['REMOTE_ADDR']; // <-- you forgot that semicolon
mysql_query("INSERT INTO ipadress
(name, ipadress) VALUES('$cheese', '$cheesy' ) ")
or die(mysql_error());


echo "Data Inserted!";

?>

[keyboard]

mysql_real_escape_string

What does that do?

[traq]

it adds slashes in front of characters that have special meaning in sql statements (specifically, \x00, \n, \r, \, ', " and \x1a) in order to make sure your input is treated as a string and nothing in the string causes parse errors or can be used in an attack. Learn more here (http://us2.php.net/manual/en/security.database.sql-injection.php).

when in doubt, use it on every value you insert into a mysql statement. If nothing needs to be escaped, then it won't do any harm.

[keyboard]

Hey everyone,
The script to write in their name and ip adress now works. I'm trying to retrieve the data now.


<?php

mysql_connect("local_host", "****", "****") or die(mysql_error());
mysql_select_db("****") or die(mysql_error());


$result = mysql_query("SELECT * FROM example
WHERE ipadress='$_SERVER['REMOTE_ADDR'];'") or die(mysql_error());


$row = mysql_fetch_array( $result );
echo $row['name']." - ".$row['ipadress'];
?>


It comes up with this error.

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home1/keyboard/public_html/databaseretrieve.php on line 8


Any help would be appreciated.

[djr33]

I don't see the problem. Which is line 8? If you comment it out does the parse error disappear? (The script won't work properly, but that will help you debug it-- first step is finding the error.)

One problem is that you have the semi-colon and close-single-quote reversed in the query. This shouldn't be causing a PHP parse error but MySQL won't like it. Switch ;' to ';.

Secondly what happens if you put braces around this:
$_SERVER['REMOTE_ADDR']
So it becomes
{$_SERVER['REMOTE_ADDR']}
(Within the query, everything else as-is.)

But I'm still not sure what is causing the parse error. You can find it by trial and error by commenting out lines or by simplifying them so they parse (just temporarily). After you find the problem then look into fixing it.

The only thing that looks a bit odd regarding whitespace to me is the following:
$row = mysql_fetch_array( $result );
Remove the spaces around $result. I wouldn't expect that to be a problem, but I also never have spaces like that, so it's something to check.

Also, try removing the line break in the MySQL query. That's also slightly unusual whitespace. But I'm almost positive that is unrelated because it's within a string.

[keyboard]

<?php

mysql_connect("local_host", "****", "****") or die(mysql_error());
mysql_select_db("****") or die(mysql_error());


// $result = mysql_query("SELECT * FROM example
WHERE ipadress='$_SERVER['REMOTE_ADDR']';") or die(mysql_error());


$row = mysql_fetch_array($result);
echo $row['name']." - ".$row['ipadress'];
?>

Now this error comes up

Parse error: syntax error, unexpected T_STRING in /home1/keyboard/public_html/databaseretrieve.php on line 8

[keyboard]

Also tried this


<?php

mysql_connect("local_host", "****", "****") or die(mysql_error());
mysql_select_db("****") or die(mysql_error());


$result = mysql_query("SELECT * FROM example
WHERE ipadress='{$_SERVER['REMOTE_ADDR']}';") or die(mysql_error());


$row = mysql_fetch_array($result);
echo $row['name']." - ".$row['ipadress'];
?>



and now it comes up with this


Warning: mysql_connect() [function.mysql-connect]: Unknown MySQL server host 'local_host' (1) in /home1/keyboard/public_html/databaseretrieve.php on line 3
Unknown MySQL server host 'local_host' (1)

[djr33]

First, you can't comment out half of a line. As I said, it's best to put a single line on a single line within the file-- you have an extra line break in the middle of your SQL query. There's no need for that. (It probably also won't hurt anything.) So either put the MySQL query on a single line (remove the line break before "WHERE"), or comment out BOTH lines (because they represent a single line of code) to see if that is the problem-- from what you've posted it does seem that that section is the problem.

Second, it's "localhost" not "local_host" and make sure you're actually using your information (username, password) rather than the stars. (But of course don't post those here.)

As for the other changes you made regarding the variable within the string, that will probably be fine. But you'll need to fix the other two things first. Now that you have a MySQL error that means there is no longer a parse error. Parse errors occur before the code is used at all because PHP does not understand what to do. A MySQL error (like many other kinds of errors) is a runtime error, something that happens while the script is being processed.

Anyway, it seems that if you fix your MySQL information this script should work, or at least you will move on to the next part of debugging.

I think the original problem was in the way you used an array key without brackets {} inside the string. That seems fixed now.

[traq]

"SELECT * FROM example WHERE ipadress='{$_SERVER['REMOTE_ADDR']}';"


while this will generate a correctly formed SQL statement, you should not include the ending semicolon (http://www.php.net/manual/en/function.mysql-query.php#refsect1-function.mysql-query-parameters) when using mysql_query(). I suspect this has to do with how mysql_query() is designed to execute only one statement at a time (the semicolon ends the statement and makes ready to begin a new one) and because the function tries to add the semicolon itself when run.

[djr33]

Really? I always add the semi-colon to avoid any possible injection (although that's avoided in other ways already). I was under the impression that two queries could be run with it, but I can't remember ever trying that. Your link confirms what you're saying... interesting. Thanks for mentioning it.

[traq]

dunno, really. :D I've never tried two statements at once either. it does say "should not," though, not "must not." maybe it's the kind of thing that can cause problems in older php/sql versions or on particular server setups. who knows...

[keyboard]

Thanks so much everyone