I have an INSERT statement in a PHP-file wherein at-signs (@) are occurring in front of the column name.
@field1, @field2,
It is a MySQL database. What does the at-sign mean?
Edit:
There is no SET @field1 := 'test'
in the PHP script. The PHP script reads a csv and puts the data into the table. Can it be misused as a commenting out feature?
<?php
$typo_db_username = 'xyz'; // Modified or inserted by TYPO3 Install Tool.
$typo_db_password = 'xyz'; // Modified or inserted by TYPO3 Install Tool.
// login
$_SESSION['host'] = "localhost";
$_SESSION['port'] = "3306";
$_SESSION['user'] = $typo_db_username;
$_SESSION['password'] = $typo_db_password;
$_SESSION['dbname'] = "database";
$cxn = mysqli_connect($_SESSION['host'], $_SESSION['user'], $_SESSION['password'], $_SESSION['dbname'], $_SESSION['port']) or die ("SQL Error:" . mysqli_connect_error() );
mysqli_query($cxn, "SET NAMES utf8");
$sqltrunc = "TRUNCATE TABLE tablename";
$resulttrunc = mysqli_query($cxn,$sqltrunc) or die ("Couldn’t execute query: ".mysqli_error($cxn));
$sql1 = "
LOAD DATA LOCAL
INFILE 'import.csv'
REPLACE
INTO TABLE tablename
FIELDS
TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES
(
`normalField`,
@field1,
@field2,
`normalField2`,
@field3,
@field4
)";
$result1 = mysqli_query($cxn,$sql1) or die ("Couldn’t execute query: " . mysqli_error($cxn));
?>'
SOLUTION:
Finally, I found it out! The @ field is used as dummy to miss out a column in a csv-file. See http://www.php-resource.de/forum/showthread/t-97082.html http://dev.mysql.com/doc/refman/5.0/en/load-data.html
The @CustID means it's a parameter that you will supply a value for later in your code. This is the best way of protecting against SQL injection. Create your query using parameters, rather than concatenating strings and variables.
T-SQL (Transact-SQL) is a set of programming extensions from Sybase and Microsoft that add several features to the Structured Query Language (SQL), including transaction control, exception and error handling, row processing and declared variables.
Using special characters in column names can create a problem for the SQL queries that are used by Netcool/Impact. Netcool/Impact, in most cases, surrounds the column that contains the special characters with double quotation marks to avoid this issue. However, if the particular character is not listed in the impact.
The @ sign is a variable in SQL. In MySQL it is used to store a value between consecutive runs of a query, or to transfer data between two different queries. An example. Transfer data between two queries.
The @
sign is a variable in SQL.
In MySQL it is used to store a value between consecutive runs of a query, or to transfer data between two different queries.
An example
Transfer data between two queries
SELECT @biggest:= MAX(field1) FROM atable;
SELECT * FROM bigger_table WHERE field1 > @biggest;
Another usage is in ranking, which MySQL doesn't have native support for.
Store a value for consecutive runs of a query
INSERT INTO table2
SELECT @rank := @rank + 1, table1.* FROM table1
JOIN( SELECT @rank := 0 ) AS init
ORDER BY number_of_users DESC
Note that in order for this to work, the order in which the rows get processed in the query must be fixed, it's easy to get this wrong.
See:
http://dev.mysql.com/doc/refman/5.0/en/user-variables.html
mysql sorting and ranking statement
http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/
UPDATE
This code will never work.
You've just opened the connection before and nowhere are the @fields set.
So currently they hold null
values.
To top that, you cannot use @vars to denote fieldnames, you can only use @vars for values.
$sql1 = "
LOAD DATA LOCAL INFILE 'import.csv'
REPLACE INTO TABLE tablename
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES
(`normalField`, @field1, @field2, `normalField2`, @field3, @field4)";
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With