Php pdo alter table

Добавить / удалить столбец MySQL в существующую таблицу через PHP / PDO с определенным пользователем именем и типом данных

Я хочу, чтобы пользователи моего веб-приложения PHP могли вставлять столбцы в некоторые таблицы, не требуя от кого-либо знания SQL. Каков наилучший способ сделать это, очевидно, что проблемы с SQL-инъекцией и id-10-t являются проблемой. Могу ли я просто использовать

sql = 'ALTER TABLE table_name ADD ? ?' 

PDO не сможет связать их как params. Я бы рекомендовал вместо этого проверять имя столбца по регулярному выражению, которое разрешает только ^[a-z0-9_]+$ , которое защищает имя таблицы от внедрения SQL и допускает только выбираемые параметры для типа данных, также проверенные по массиву допустимых значений в вашем коде.

если вы сделаете первый комментарий ответом, я отмечу его как лучший, вы можете объяснить этот шаблон регулярных выражений? Я хотел бы разрешить только буквы, цифры и подчеркивания

3 ответа

К сожалению, вы не можете связать имя столбца или свойства в инструкции ALTER TABLE с параметрами PDO. Вместо этого, в качестве защиты от SQL-инъекции, я бы рекомендовал проверить имя столбца на регулярное выражение, которое допускает только буквы, цифры и символы подчеркивания, и обязательно указывать имя столбца в инструкции.

if (!preg_match('/^[a-z0-9_]+$/i', $column_name)) < // Fail. submitted column name doesn't match regex >

• ^ = начало строки
• [a-z0-9_]+ = одна или несколько букв, цифр, подчеркивание
• $ = конец строки
• /i = без учета регистра

Затем, чтобы проверить тип данных столбца, вы должны разрешать только определенный набор параметров и проверять их с помощью массива. Это называется белым списком.

// Allowed types: $acceptable_types = array('VARCHAR(200)', 'VARCHAR(10)', 'INT', 'FLOAT', 'etc. '); if (!in_array($column_data_type, $acceptable_types)) < // Fail. Invalid type submitted >

Вышеуказанные параметры лучше всего обслуживать через список . Вы также можете указать дополнительные параметры, такие как NULL/NOT NULL как входные флажки для создания инструкции ALTER TABLE .

// Only after everything is successfully validated: $sql = "ALTER TABLE 'tablename' ADD '$valid_column_name' $valid_column_datatype $NULL_option"; 

Вы можете рассмотреть мета-таблицу для дополнительных столбцов.

Если вы установили данные для каждого вида автомобилей (например, вес снаряжения, высоты и т.д.), А затем другую специализированную информацию, которую добавляет пользователь, которая НЕ применяется к каждому автомобилю, вы можете сохранить его в отдельной таблице, например:

CAR TABLE: идентификатор curb_weight height.

META TABLE: id (первичный ключ) car_id (внешний ключ) data_name data_value

Мета-таблица имеет отношение «один ко многим» с таблицей «Автомобиль». Если пользователь хочет добавить столбец для двух конкретных автомобилей для записи «размера зеркала» или чего-то другого, таблица Meta получит две записи с автоматическим идентификатором, идентификатором автомобиля, «Размер зеркала» и размером.

Если вы хотите иметь список имен данных, вы можете использовать третью таблицу и иметь мета-таблицу для хранения идентификаторов, car_id и data_id.

Источник

Mysql – ALTER TABLE with PDO and parameters

Is it possible to add parameters with ALTER TABLE with PDO.

$q = $dbc -> prepare("ALTER TABLE emblems ADD ? TINYINT(1) UNSIGNED NOT NULL DEFAULT '0', ADD ? DATETIME NOT NULL"); $q -> execute(array($emblemDB, $emblemDB . 'Date')); 

Best Solution

The nature of alter table queries, to my knowledge are not prepared statements. But you should call the beginTransaction and commit() functions for most table altering queries.

$dbh->beginTransaction(); /* Change the database schema and data */ $sth = $dbh->exec("DROP TABLE fruit"); $sth = $dbh->exec("UPDATE dessert SET name = 'hamburger'"); $sth = $dbh->exec("ALTER TABLE `dessert` ADD `field1` VARCHAR(24) NOT NULL"); /* Commit changes */ $dbh->commit(); 

Although you could use the prepared statments and execute as far as I know.

NOTE:
MySQL implicitly calls the commit() function on CREATE TABLE and DROP TABLE queries, so rollback is not possible.

Also, if you’re wanting to pass variables to an alter table query, make sure to sanitize your user input (if that’s where it’s coming from), and create a stored procedure on your db, then call it using PDO and attach your variables for inout. Just a thought in regards to how your question was worded.

Related Solutions

Php – Are PDO prepared statements sufficient to prevent SQL injection

The short answer is NO, PDO prepares will not defend you from all possible SQL-Injection attacks. For certain obscure edge-cases.

I’m adapting this answer to talk about PDO.

The long answer isn’t so easy. It’s based off an attack demonstrated here.

The Attack

So, let’s start off by showing the attack.

$pdo->query('SET NAMES gbk'); $var = "\xbf\x27 OR 1=1 /*"; $query = 'SELECT * FROM test WHERE name = ? LIMIT 1'; $stmt = $pdo->prepare($query); $stmt->execute(array($var)); 

In certain circumstances, that will return more than 1 row. Let’s dissect what’s going on here:

SELECT * FROM test WHERE name = '縗' OR 1=1 /*' LIMIT 1 

Congratulations, you just successfully attacked a program using PDO Prepared Statements.

The Simple Fix

Now, it’s worth noting that you can prevent this by disabling emulated prepared statements:

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); 

This will usually result in a true prepared statement (i.e. the data being sent over in a separate packet from the query). However, be aware that PDO will silently fallback to emulating statements that MySQL can’t prepare natively: those that it can are listed in the manual, but beware to select the appropriate server version).

The Correct Fix

The problem here is that we didn’t call the C API’s mysql_set_charset() instead of SET NAMES . If we did, we’d be fine provided we are using a MySQL release since 2006.

If you’re using an earlier MySQL release, then a bug in mysql_real_escape_string() meant that invalid multibyte characters such as those in our payload were treated as single bytes for escaping purposes even if the client had been correctly informed of the connection encoding and so this attack would still succeed. The bug was fixed in MySQL 4.1.20, 5.0.22 and 5.1.11.

But the worst part is that PDO didn’t expose the C API for mysql_set_charset() until 5.3.6, so in prior versions it cannot prevent this attack for every possible command! It’s now exposed as a DSN parameter, which should be used instead of SET NAMES .

The Saving Grace

As we said at the outset, for this attack to work the database connection must be encoded using a vulnerable character set. utf8mb4 is not vulnerable and yet can support every Unicode character: so you could elect to use that instead—but it has only been available since MySQL 5.5.3. An alternative is utf8 , which is also not vulnerable and can support the whole of the Unicode Basic Multilingual Plane.

Alternatively, you can enable the NO_BACKSLASH_ESCAPES SQL mode, which (amongst other things) alters the operation of mysql_real_escape_string() . With this mode enabled, 0x27 will be replaced with 0x2727 rather than 0x5c27 and thus the escaping process cannot create valid characters in any of the vulnerable encodings where they did not exist previously (i.e. 0xbf27 is still 0xbf27 etc.)—so the server will still reject the string as invalid. However, see @eggyal’s answer for a different vulnerability that can arise from using this SQL mode (albeit not with PDO).

Safe Examples

The following examples are safe:

mysql_query('SET NAMES utf8'); $var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*"); mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1"); 

Because the server’s expecting utf8 .

mysql_set_charset('gbk'); $var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*"); mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1"); 

Because we’ve properly set the character set so the client and the server match.

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $pdo->query('SET NAMES gbk'); $stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1'); $stmt->execute(array("\xbf\x27 OR 1=1 /*")); 

Because we’ve turned off emulated prepared statements.

$pdo = new PDO('mysql:host=localhost;dbname=testdb;charset=gbk', $user, $password); $stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1'); $stmt->execute(array("\xbf\x27 OR 1=1 /*")); 

Because we’ve set the character set properly.

$mysqli->query('SET NAMES gbk'); $stmt = $mysqli->prepare('SELECT * FROM test WHERE name = ? LIMIT 1'); $param = "\xbf\x27 OR 1=1 /*"; $stmt->bind_param('s', $param); $stmt->execute(); 

Because MySQLi does true prepared statements all the time.

Wrapping Up

• Use Modern Versions of MySQL (late 5.1, all 5.5, 5.6, etc) AND PDO’s DSN charset parameter (in PHP ≥ 5.3.6)

• Don’t use a vulnerable character set for connection encoding (you only use utf8 / latin1 / ascii / etc)

Otherwise, you’re vulnerable even though you’re using PDO Prepared Statements.

Addendum

I’ve been slowly working on a patch to change the default to not emulate prepares for a future version of PHP. The problem that I’m running into is that a LOT of tests break when I do that. One problem is that emulated prepares will only throw syntax errors on execute, but true prepares will throw errors on prepare. So that can cause issues (and is part of the reason tests are borking).

Php – Getting a PDO query string with bound parameters without executing it

If you want to just emulate it, try:

echo preg_replace('?', $username, $result->queryString); 

Источник

Изменить таблицу MySQL с помощью PDO и подготовленного оператора

Я использовал PDO в своем проекте с PHP и MySQL. Все работает нормально, но у меня возникает ошибка при выполнении кода ниже:

$sql = "ALTER TABLE tbl_invoices AUTO_INCREMENT = ?"; $q = $db->prepare($sql); $q->execute(array($invoice_start)); 

Неустранимая ошибка: необработанное исключение «PDOException» с сообщением «SQLSTATE [42000]: синтаксическая ошибка или нарушение прав доступа: 1064. У вас есть ошибка в синтаксисе SQL; проверьте руководство, соответствующее версии вашего сервера MySQL, для правильного синтаксиса для использования рядом с ‘?’ на линии.

Ответы (1)

Никогда. Всегда. Трогать. Автоинкремент.

Каждый раз, когда вы принимаете это как число, это означает серьезный недостаток в структуре базы данных. Что касается изменения таблиц в целом — да, большую часть времени вы не можете выполнить привязку.

Поскольку мы никогда не должны касаться автоинкремента, неужели это ужасная идея использовать поле ИИ для порядка? Например, эта строка была вставлена ​​перед этой? — person bassxzero; 21.11.2016

1) ON DUPLICATE KEY UPDATE будет увеличивать значение автоинкремента, когда запись существует. Возможность предотвратить создание больших промежутков в автоинкременте может быть приятной. 2) MySQL и MariaDB не позволяют вам установить значение ниже максимального. 3) Если вы хотите убедиться, что не создаются значения ниже определенного значения (например, если вы хотите позже импортировать данные, которые, как вы знаете, имеют максимальный идентификатор), также полезно установить идентификатор AUTO_INCREMENT. — person Frank Forte; 20.01.2018

Источник